Outer Join to more than one table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Outer Join to more than one table

Hybrid View

  1. #1
    Join Date
    Sep 2005
    Posts
    46

    Outer Join to more than one table

    Hai every body,

    I need to write a query where a table is to be outer joined to two tables. But as you know its not allowed in SQL to have outer join to more than one table. Can any body suggest a work around?

    For example if I have emp, dept and club with empid, deptid, clubid being the respective primary keys. if I need to select all the employees regard less of whether they have a club or department, the ideal query will be :

    SELECT e.empid, e.empname, d.deptname, c.clubname
    FROM emp e, dept d, club c
    WHERE e.deptid = d.deptid(+) AND
    e.clubid = c.clubid(+)

    which is illegal...

    If any of new an alternative for this .. please suggest..

    regards
    Issac

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The SQL is valid ... what would not bevalid would be:
    Code:
    SELECT e.empid, e.empname, d.deptname, c.clubname
    FROM emp e, dept d, club c
    WHERE e.deptid(+) = d.deptid AND
    e.clubid(+) = c.clubid
    To do the above you would outerjoin one table to another in an inline view, then outerjoin the inline view to the third table
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I'd add that most times I've hit "no outer join to more than one table", it's been because I've miss-coded the business requirements.

    If we take Slim's example: if it were allowed, it would produce a Cartesian product of dept and club - you might want that, but I think it's unlikely.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Quote Originally Posted by issacgeorge
    Hai every body,

    I need to write a query where a table is to be outer joined to two tables. But as you know its not allowed in SQL to have outer join to more than one table. Can any body suggest a work around?

    For example if I have emp, dept and club with empid, deptid, clubid being the respective primary keys. if I need to select all the employees regard less of whether they have a club or department, the ideal query will be :

    SELECT e.empid, e.empname, d.deptname, c.clubname
    FROM emp e, dept d, club c
    WHERE e.deptid = d.deptid(+) AND
    e.clubid = c.clubid(+)

    which is illegal...

    If any of new an alternative for this .. please suggest..

    regards
    Issac
    SELECT e.empid, e.empname, d.deptname,
    (select c.clubname from club c
    where c.clubid = e.clubid ) CLUBNAME
    FROM emp e, dept d
    WHERE e.deptid = d.deptid(+)

    Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width