sqlsql-serverjoinselectinner-join

How do I write a query for inner join

enter image description here

I want to write a select query that shows me first_name, last_name and the management_name. Each employee row has a management column, which is equal to the employee_id of the manager.

This is what I wrote, but it doesn't work well:

select 
  e.first_name , 
  e.last_name , 
  o.firstname AS management_name 
from employee e
join employee o  ON e.employee_id = o.management
Solution

  • If you intend that employee e is the subordinate and employee o is their manager, then you have the join condition reversed. The manager's employee_id should be matched up with the subordinate's management value with ON e.management = o.employee_id.

    The corrected query would then be:

    select 
  e.first_name , 
  e.last_name , 
  o.firstname AS management_name 
from employee e
join employee o  ON e.management = o.employee_id

    Also, if you wish your results to show that employee "Chandler Bing" has no manager, you should change your inner join to a left join.