SQL CTE Recursion: Returning Parent Records
I am currently running a CTE query to recursively build an employee hierarchy from an employees table similar to what most recursive examples demonstrate. Where I am stuck is that I am trying to query for a single employee and retrieve the hierarchy above him. Below is an example of the table I am trying to work with:
Employees =========================================================================== EmployeeID MgrID Name 1 null Joe 2 1 John 3 2 Rob 4 2 Eric
The following is the SQL that allows me to display the hierarchy from the top down:
with employeeMaster as ( select p.EmployeeID, p.MgrID, p.Name from Employees p where p.MgrID is null union all select c.EmployeeID, c.MgrID, c.Name from employeeMaster cte inner join Employees c on c.MgrID = cte.EmployeeID ) select * from employeeMaster
Where I am stuck is that I can’t figure out how to query for the lowest level employee, either Rob or Eric, and return the hierarchy above him from Joe > John > Eric. It seems as though this should be easy but I can’t spot it for the life of me.
One Solution collect form web for “SQL CTE Recursion: Returning Parent Records”
Are you looking for a query to return a variable number of columns, depending on the depth of hierarchy? Or just a concatenated string in one field?
Here’s a minor change to your query that will get Eric and anyone above him in the hierarchy.
WITH employeeMaster AS ( SELECT p.EmployeeID , p.MgrID , p.NAME FROM Employees p WHERE p.NAME = 'Eric' UNION ALL SELECT c.EmployeeID , c.MgrID , c.NAME FROM employeeMaster cte INNER JOIN Employees c ON c.EmployeeID = cte.MgrID ) SELECT * FROM employeeMaster m