Re: outer join to 2 tables in view definition??

To answer my own question:

Now I did:  

 select data.a, data.b, data.c, translation.trans    from data, translation, parent
  where data.parent_id = parent.id
    and parent.lang = translation.lang
    and data.c = translation.org
  union all
 select data.a, data.b, data.c, NULL trans    from data, parent
  where data.parent_id = parent.id
    and not exists (select trans from translation tr

                     where parent.lang = tr.lang
                       and data.c = tr.org)

That delivers the result I was looking for but if anyone has a better and more efficient solution I'd really appreciate it. Efficiency is quite a concern as this is the definition of a view.

Thanks

   Christian

kiel wrote:
>
> Hi,
> this probably demonstrates my hopeless lack of knowledge of outer joins,
> but so be it:
>
> I have a select like this
>
> select data.a, data.b, data.c, translation.trans
> from data, translation, parent
> where data.parent_id = parent.id
> and parent.lang = translation.lang(+)
> and data.c = translation.org(+)
>
> That fails since I can't outer join translation to more than one table.
> What I'd like to do, is get the data.(a,b,c) returned even, if I don't
> have a translation for data.c in translation.
> If I take away the (+) where I join parent and translation, the query
> will just not return records for which there is no translation of data.c
> in translation.
>
> Hope that makes sense, and thanks in advance for any clues on how to
> work around this.
>
> Christian
Received on Fri Feb 05 1999 - 18:48:28 CST