Re: Outer Join to Multiple Tables

Alvin Sylvain wrote:
> ------------------ I'm converting an application from Sybase to Oracle, and I've run
 into a number of quandries.  

 In this case, Sybase allowed us to perform an outer join to several  tables besides the "main" one, and Oracle apparently will only do  this for one.  

 Eg: Say you have a personnel table, with a join to a company  table, but the person might not be employed, and a join to a  school table, but the person might not be in school, etc.  

    select p.name, c.name, s.name
    from personnel p, company c, school s     where 1=1

      and p.companyname (+) = c.name
      and p.schoolname  (+) = p.name
 

 We could do this in Sybase. If the person was both unemployed  and not in school, you'd simply get his name and two nulls. If  he was in school, you'd get his name, a null, and the school's  name. Etc.  

 Oracle complains very explicitly that you can only perform an  outer join if the table is outer joined to exactly one other  table. This sucks.  

 I suppose I could add an "empty" row with the value "'NULL'"  for the key, set all the linking columns to "'NULL'", and remove  the outer join: but that seems to me as being an awful lot of  trouble, and doesn't at all match the intent of the query.  

 Or am I making some wrong assumptions?
> ------------------

You can do what you are seeking with Oracle, you just need to get the SQL syntax correct...
Instead of

    select p.name, c.name, s.name
    from personnel p, company c, school s     where 1=1

      and p.companyname (+) = c.name
      and p.schoolname  (+) = p.name;

    select p.name, c.name, s.name
    from personnel p, company c, school s     where 1=1

      and p.companyname = c.name(+)
      and p.schoolname  = p.name(+);

John Elliott
elliotjs_at_dmdcwest.fmp.osd.mil
<standard disclaimer here> Received on Mon Jan 13 1997 - 00:00:00 CST