CODEWITHSUNDEEP
sqlrecursioncommon-table-expression
Sabrina Tessier
Sabrina Tessier

Reputation: 79

Recursive CTE that gets Employees by Manager

I am writing a recursive CTE that fetches a list of all employees and their managers. So far I have this-

WITH GetEmployeeByManager as 
(
--Anchor Query
SELECT FirstName, LastName, DepartmentID,ManagerEmployeeID
FROM dbo.Employees
WHERE ManagerEmployeeID IS NULL

Union ALL

--Recursive Query
SELECT FirstName, LastName, DepartmentID, ManagerEmployeeID
FROM dbo.Employees 
WHERE ManagerEmployeeID IS NOT NULL
)
SELECT *
FROM GetEmployeeByManager

Output so far
I need to be able to have a column called "Manager First Name" and "Manager Last Name" which takes each value from the "Manager Employee ID" column and uses it to find the correct first and last name from the original employee table. I have tried many different strategies and nothing seems to work. Any suggestions? Thank you.

Upvotes: 0

Views: 859

Answers (1)

Henning Koehler
Henning Koehler

Reputation: 2637

This doesn't require a recursive query, but simply a left join of the table with itself:

SELECT e.FirstName, e.LastName, e.DepartmentID,
       m.FirstName as ManagerFirstName, m.LastName as ManagerLastName
FROM dbo.Employees e
LEFT OUTER JOIN dbo.Employees m ON m.EmployeeID = e.ManagerEmployeeID

That's assuming you only want each employee's direct manager.

Upvotes: 4

Related Questions