SQL Server - Self Join

Question

I have a table as follows

EmployeeID  Name    ManagerID   
2           David   3
3           Roger   NULL
4           Marry   2
5           Joseph  2
7           Ben     2

Here Roger is Top Manager Mike & David are Managers And rest all are employees

I am looking for output like this:

EmployeeName    ManagerName     TopManager
Marry           David           Roger
Joseph          David           Roger
Ben             David           Roger
NULL            David           Roger

I tried using Self Join like:

SELECT e1.Name EmployeeName, ISNULL(e2.name, 'Top Manager') AS ManagerName
FROM Employee e1
LEFT JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID

but it is not giving the output I am looking for.

Answer 1

I think you have to do self join twice to get the desired output.

I created the query in this way and got the output which you have mentioned. Please note that I did not include the last row which has null value, and rest the same as it is.

Query:

create table Employees (EmployeeID int, Name varchar(10), ManagerID int) 

Insert into Employees values 
(2, 'David'  , 3   )
,(3, 'Roger'  , NULL)
,(4, 'Marry'  , 2    )
,(5, 'Joseph' , 2    )
,(7, 'Ben'    , 2    )

select e.name as EmployeeName,  e1.name ManagerName,    e2.Name TopManager  
from Employees e 
left join Employees e1 on e.ManagerID = e1.employeeid 
left join Employees e2 on  e1.ManagerID = e2.EmployeeID 
where e.ManagerID is not null  and e1.ManagerID is not null 

Where condition was given to restrict the manager names in Employee column.

Output:

EmployeeName    ManagerName  TopManager
Marry           David        Roger
Joseph          David        Roger
Ben             David        Roger

Answer 2

If you can have different top managers, then a recursive CTE is needed:

with cte as (
      select employeeid, name, name as topmanager
      from Employee
      where managerid is null
      union all
      select t.employeeid, t.name, cte.topmanager
      from Employee t join
           cte
           on t.managerid = cte.employeeid
    )
select *
from cte;

If there is only one top manager, then:

select e.*, topm.name as topmanager
from employee e cross join
     (select e2.* from employee e2 where e2.managerid is null) as topm