Outer Join to more than one table

[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

[slimdave]

The SQL is valid ... what would not bevalid would 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

To do the above you would outerjoin one table to another in an inline view, then outerjoin the inline view to the third table

[DaPi]

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.

[tamilselvan]

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