-
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
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|