-- Recursive CTE to return the management chain leading all the way up to the CEO for a specified employee

-- Recursive CTE to return the management chain leading all the way up to the CEO for a specified employee

With EmpCTE as(
Select empid,mgrid, firstname, lastname, 0 as distance
from HR.Employees
where empid = 9

Union ALL

Select M.empid,M.mgrid, M.firstname, M.lastname, S.distance+1 as distance
from EmpCTE As S
Inner Join HR.Employees As M ON M.empid = S.mgrid
)
Select * from EmpCTE