Re: outer join to 2 tables ??

From: kiel <kiel_at_webpre.com>
Date: Mon, 08 Feb 1999 13:09:25 -0800
Message-ID: <36BF5285.5DEDAF58@webpre.com>

John Koo wrote:
>
> 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
>
> If you fail to use outer join twice to two different tables, why not just
> try to
> outer join each table and then UNION the results ? Look stupid but
> logically works.
>

Maybe I don't quite understand correctly what you mean, but as I understand you that doesn't give the same result. I emulated the outer join to 2 tables by UNIONing 2 normal joins. One being the query I originally had (just without the (+)s ), the second containing a WHERE NOT EXISTS (select ... matches from the table to be outer joined)

Anyway the problem with the UNION approach is performance, as you basically have to run the whole query twice. A kind soul emailed me this solution using an inline view (Thanks for that) :

select d.a, d.b, d.c, tr.trans
  from translation trans,

       (select a, b, c, lang from parent, data
        where data.parent_id = parent.id) d

 where d.lang = trans.lang(+)
   and d.c = trans.org(+)

That works fine. I still have to play with the view this creates. This inline view approach probably doesn't make the underlying indexes available to queries on the view. So the UNION solution might be the more efficient one after all.

Thanks for everybody's help

  Christian Received on Mon Feb 08 1999 - 15:09:25 CST