Re: SQL:Joining same table more than once

In article <4amtap$ko4$7_at_mhafm.production.compuserve.com>, 74160.645_at_CompuServe.COM says...
>to do an outer join rather than COL IS NULL. Problem is that you
>can only outer join one table to another once per query.

This is only partially correct.

The [SQL Language Reference Manual p.4-372] states: "You cannot outer join the same table to more than one other table in a single SELECT statement."

Obviously this doesn't hold if a table is aliased and used more than once in a single statement.

With tables A(f1,f2) , B(f3,f4) and C(f5,f6) you can try :

   select * from A,B,C where A.f1 = B.f3 (+) and B.f4 (+) = C.f5 This leads to the error message
  ORA-01417: a table may be outer joined to at most one other table because the _same_ table is outer to more than one table

but on the other hand

   select * from A,B x1,B x2,C
   where A.f1 = x1.f3 (+) and x2.f4 (+) = C.f5 and C.f5=A.f1 will work fine because x1 and x2 are not the same table (as far as the statement is concerned)

(I tried this out on 7.1.3.3.12 on NT3.51 )

greetings

-- 
    _/_/_/   _/_/_/ _/    _/  // Reinhard Kuhn             /  It can be      
   _/    _/ _/     _/  _/    //         (kuhn_at_cas-ps.com) /  done quickly,   
  _/_/_/   _/_/_/ _/_/      // CAS GmbH                  /  cheaply or well  
 _/  _/   _/     _/  _/    // Lemberger Strasse 14      /   - pick any two!  
_/   _/  _/_/_/ _/    _/  // 66955 Pirmasens, Germany  /