We help IT Professionals succeed at work.

SQL JOIN Question

pcarrollnf
pcarrollnf asked
on
182 Views
Last Modified: 2010-03-20
I have a DB that has the following tables:

EMPLOYEE table has columns EMPLOYEE_ID, EMPLOYEE_NAME with PK = EMPLOYEE_ID
MANAGERS table has columns MANAGER_ID, EMPLOYEE_ID, MANAGER_NAME with PK = MANAGER_ID, EMPLOYEE_ID
EXECUTIVES table has columns EXECUTIVE_ID, EMPLOYEE_ID, EXECUTIVE_NAME with PK = EXECUTIVE_ID, EMPLOYEE_ID

I am trying to write a query that will return all DISTINCT EMPLOYEE_IDs from the MANAGERS and EXECUTIVES tables using:

SELECT DISTINCT e.EMPLOYEE_ID FROM EMPLOYEE e, MANAGERS m, EXECUTIVES x
WHERE ( e.EMPLOYEE_ID = m.EMPLOYEE_ID OR e.EMPLOYEE_ID = x.EMPLOYEE_ID )

There are rows in the EMPLOYEES and MANAGERS tables but there are no rows in the EXECUTIVES table.  When I execute the query, no results are returned.  However, if I place 1 row in the EXECUTIVES table then the query returns the expected rows.  Why is the query not returning any rows when the EXECUTIVES table is empty?
Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I need a solution that is generic enough to work in both Oracle and SQL Server.  Is the SQL Server answer generic SQL that may be used in Oracle also?

Commented:
Yes the SQL server answer is generic it works in both oracle and SQL server
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.