pcarrollnf
asked on
SQL JOIN Question
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes the SQL server answer is generic it works in both oracle and SQL server
ASKER