Re: SQL 9i OUTER JOIN QUERY

Hi, Rahul. What are you trying to do? Saying 'WHERE E.DEPARTMENT_ID IN (D.DEPARTMENT_ID)' doesn't even look like legal SQL to me. And I can't imagine what '(+)' could mean in that construct. What happened to the DEPARTMENT_ID = 100 in the example using IN? Since you are joining over DEPARTMENT_ID, you are going to get all of them anyway.

As a beginning, you should restructure your syntax to eliminate the archaic outer join syntax in favor of the proper syntax. Joins do not belong in the WHERE clause. There is never a time when the long-deprecated '(+)' operator should be used.

SELECT

     E.LAST_NAME,
     D.DEPARMENT_NAME,
     D.DEPARTMENT_ID

FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D
     ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

Apologies, but I can't understand how the 'DEPARTMENT_ID = 100' fits into this. The above query will already return department 100 data as long as there is any employee in that department. If you want the department 100 data even in the case that no one is in that department, you should add a UNION to that effect:

SELECT

     E.LAST_NAME,
     D.DEPARMENT_NAME,
     D.DEPARTMENT_ID

FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D
     ON E.DEPARTMENT_ID = D.DEPARTMENT_ID

UNION SELECT

     E.LAST_NAME,
     D.DEPARMENT_NAME,
     D.DEPARTMENT_ID

FROM EMPLOYEES E RIGHT OUTER JOIN DEPARTMENTS D
     ON E.DEPARTMENT_ID = D.DEPARTMENT_ID

WHERE D.DEPARTMENT_ID = 100; If I haven't understood you and you still need more help, could you just state in English what you'd like to get back from the server, rather than posting your code, and I'll try to give you a query to get what you're looking for?

I hope this was helpful, but I'm not certain enough that I understand what you're trying to do.

Brian Received on Sat Aug 07 2004 - 16:45:03 CEST