CODEWITHSUNDEEP
mysql
Mani R
Mani R

Reputation: 40

Self join query value mismatch

I have a table called Staffs and it is having column names are id, name, phone,manager_id

When I execute the self-join query it gives the wrong output. Please advise me

# id, name, phone, manager_id
'1', 'test1', '9089', NULL
'2', 'test2', '6877', '1'
'3', 'test3', '3656', '2'
'4', 'test4', '4324', '2'
'5', 'test5', '90', '3'
'6', 'test6', '5464', '4'
'7', 'test7', '5626', '3'

the values are in staffs table

what I tried

select e.name as employee,m.name as manager
 from staffs e
 inner join staffs m 
 on m.id=e.id 

 order by manager

it gives wrong output as

# employee, manager
'test1', 'test1'
'test2', 'test2'
'test3', 'test3'
'test4', 'test4'
'test5', 'test5'
'test6', 'test6'
'test7', 'test7'

Upvotes: 0

Views: 67

Answers (1)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

Your query should look like this:

SELECT e.name as employee, m.name as manager
FROM staffs e LEFT JOIN staffs m ON e.manager_id = m.id 
ORDER BY m.name ASC

You join each staff with itself using e.id = m.id. But you want to show the manager of each staff so you have to use e.manager_id = m.id. By using a LEFT JOIN you can show all records with the name of the manager or without a manager name in case it is the manager itself.

Upvotes: 1

Related Questions