Data Science/Business Analytics for Small Business Applications › Forums › SQL › Self JOIN: Difficulty relating two copies of the same table
Tagged: SELF JOIN in SQL
- This topic is empty.
April 23, 2022 at 3:54 am #142AdminKeymaster
Here is the problem:
All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.
Retrieve a list with the managers last name, and the last name of the employees who report to him or her.
Here is my tentative solution:
SELECT E.LastName, M.LastName AS ManagersLastName FROM Employees E JOIN Employees M WHERE E.EmployeeId = M.ReportsTo
After running the query described above*, who are the reports for the manager named Mitchell (select all that apply)?*
To my understanding, Mitchell reports to King and Callahan. In other words, Mitchell is the employee whose managers are King and Callahan.
That however seems wrong as Mitchell should perhaps report to only one manager (if I am not wrong).
So, the correct answer given multiple choices it appears should be Callahan and King. However, that defies my understanding and the way I proceeded.
Any clue/help where I am going wrong appreciated. Thanks in advance!
You have your join condition around the wrong way, if you expand the aliases you can see why.
SELECT Employee.LastName, Manager.LastName AS ManagersLastName FROM Employees Employee JOIN Employees Manager ON Employee.EmployeeId = Manager.ReportsTo
Check the ON condition Employee.EmployeeId = Manager.ReportsTo, you want the EmployeeId of the manager which the Employee reports to e.g.
SELECT Employee.LastName, Manager.LastName AS ManagersLastName FROM Employees Employee JOIN Employees Manager ON Employee.ReportsTo = Manager.EmployeeId
- You must be logged in to reply to this topic.