I am learning Self-Join in HR schema Oracle 11g. I tried answering an exercise question and came up with the below query as answer -
SELECT e.LAST_NAME "EMPLOYEE'S LAST NAME", e.EMPLOYEE_ID, m.LAST_NAME "MANAGER'S LAST NAME", e.DEPARTMENT_ID
FROM EMPLOYEES e JOIN EMPLOYEES m
ON (e.EMPLOYEE_ID = m.MANAGER_ID AND e.DEPARTMENT_ID IN (10,20,30))
But the answer given has
SELECT e.LAST_NAME "EMPLOYEE'S LAST NAME", e.EMPLOYEE_ID, m.LAST_NAME "MANAGER'S LAST NAME", e.DEPARTMENT_ID
FROM EMPLOYEES e JOIN EMPLOYEES m
ON (m.EMPLOYEE_ID = e.MANAGER_ID AND e.DEPARTMENT_ID IN (10,20,30))
The change is in the JOIN...ON's aliases. I used e.EMPLOYEE_ID = m.MANAGER_ID while the correct answer is m.EMPLOYEE_ID = e.MANAGER_ID
These two conditions give two different set of records but how? Can someone explain this in simple terms, please?
If in case it will help, the question is
There is a hierarchical relationship between employees and their managers. For each row in the EMPLOYEES table the MANAGER_ID column stores the
EMPLOYEE_ID of every employee’s manager. Using a self-join on the EMPLOYEES table, you are required to retrieve the employee’s LAST_NAME, EMPLOYEE_ID, manager’s LAST_NAME, and employee’s DEPARTMENT_ID for the rows with DEPARMENT_ID values of 10, 20, or 30. Alias the EMPLOYEES table as E and the second instance of the EMPLOYEES table as M. Sort the results based on the DEPARTMENT_ID column. >
Now this gets me wondering... why did I use e.DEPARTMENT_ID in (10,20,30)? I don't know! I could have also used m.DEPARTMENT_ID IN....I am missing some basic understanding here, I think.
Edited by: TuX4EvA on Aug 20, 2009 4:04 AM