SQL CTE Recursion: Returning Parent Records

Question

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.

Answer 1

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