Re: How do I construct multiple outer joins?

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Thu, 27 Feb 2003 21:53:32 -0800
Message-ID: <3E5EF95C.4AFD4347@exesolutions.com>

Ray Teale wrote:

> Jeez I hate these generic examples......what's wrong with using emp, dept
> etc. Anyway - you can't do what you want with an outer join because table 2
> cannot join to table 3 when it joins with the outer join. You can achieve
> the result using a union rather than an outer join.
>
> Consider this example:
>
> select dept.deptno, emp.ename, locations.officename
> from locations, emp, dept
> where emp.deptno(+) = dept.deptno
> and emp.officeid = locations.officeid
> and dept.deptno = 40
>
> ... won't work because emp can't join to locations with null value for
> departments with no employees .
>
> Using a union - the first query in the union does the join between the three
> tables. The second retrieves from table three all records which are not in
> table 2.
>
> select dept.deptno, emp.ename, locations.officename
> where emp.deptno = dept.deptno
> and emp.officeid = locations.officeid
> union all
> select dept.deptno, null, null
> from dept
> where deptno not in (select deptno from emp)
>
> Regards
>
> Ray Teale
>
> "DA Morgan" <damorgan_at_exesolutions.com> wrote in message
> news:3E5CEE47.BCE90F32_at_exesolutions.com...
> > Randy Harris wrote:
> >
> > > I need help with a SQL...
> > >
> > > I have 3 tables, I've tried:
> > >
> > > SELECT Table1.Field1, Table1.Field2, Table2.Field1, Table3.Field1
> > > FROM Table1, Table2,Table3
> > > WHERE Table1.Key1 = Table2.Key1
> > > AND Table2.Key2(+) = Table3.Key2
> > > AND Table3.Field1 = 'Criteria' ;
> > >
> > > What I need is for it to return Table3.Field1 regardless of whether
> there is
> > > a matching record in Table2. It returns 0 records, I assume because
> there
> > > is no circumstance where "Table1.Key1 = Table2.Key1", since there is no
> > > matching record in Table2. Is there a way to write this SQL to do what I
> > > need?
> > >
> > > --
> > > Randy Harris
> >
> > Can't be done unless you can link Table1 to Table3 without going through
> Table2.
> > In fact, it is not just impossible in Oracle ... it is a logical
> impossibility.
> >
> > Daniel Morgan
> >

What he was trying to do was logically impossible but there is a simple workaround in Oracle for multiple outer joins. It looks like this:

SELECT a.field1, d.field2
FROM sometable a,

            (SELECT field2, field3
             FROM anothertable b, stillanother c
             WHERE b.field = c.field (+)) d

WHERE a.field = d.field3 (+);

One outer join inside an in-line view ... another outer-join between a table and the in-line view. This method allows nesting many outer-joins into a single statement.

Daniel Morgan Received on Thu Feb 27 2003 - 23:53:32 CST