Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
hi guys

I'm confused.

How can I get manager list?! 

Why can not I get an answer?

SQL
EmployeeID   name   manager
   1         john    NULL
   2         sara     1
   3         philip   1
   4         tomas    3
   5         celena   2


What I have tried:

SQL
SELECT Name FROM Employee WHERE EmployeeID = manager
Posted
Updated 13-Jan-17 22:59pm

1 solution

If you are after a list of manager names, then that's not too difficult:
First write a SELECT query to return just the IDs of Managers:
SQL
SELECT DISTINCT Manager FROM Employee WHERE Manager IS NOT NULL
That returns:
Manager
1
2
3
Then use that to JOIN to the original table:
SQL
SELECT a.[Name] FROM Employee a
JOIN (SELECT DISTINCT Manager FROM Employee WHERE Manager IS NOT NULL) b
ON a.EmployeeID = b.Manager
That gives you a list of manager names:
Name
john      
sara      
philip
If you want the employee and their manager it's very similar:
SQL
SELECT a.[Name] AS [Manager name], b.Name AS [Supervised] FROM Employee a
JOIN (SELECT DISTINCT Manager, Name FROM Employee WHERE Manager IS NOT NULL) b
ON a.EmployeeID = b.Manager
Which will give you:
Manager name	Supervised
John      	philip    
John      	sara      
sara      	celena    
philip    	tomas
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900