Re: How do I construct multiple outer joins?

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
>
Received on Thu Feb 27 2003 - 22:41:23 CST