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.

Jamie F · Accepted Answer · 2011-03-03 23:50:56Z

8

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

edited Mar 3, 2011 at 23:50

answered Mar 3, 2011 at 23:23

Jamie F

23.9k5 gold badges64 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tehrab Over a year ago

Using the table example in my post, I would like to query for Eric and return a recordset with three records showing his record and the two people above him. The end goal being an abilty query any employee and see that employee's chain of command all the way to the top.

Tehrab Over a year ago

Always the simple stuff. Marked as answered.

Collectives™ on Stack Overflow

SQL CTE Recursion: Returning Parent Records

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related