Geek Answers Handbook

CTE SQL Recursion: Returning Parent Records

I am currently running a CTE query to recursively assemble an employee hierarchy from an employee table, similar to what the most recursive examples demonstrate. Where am I stuck in trying to get one employee and get a hierarchy over him. The following is an example of a table I'm trying to work with:

Employees
===========================================================================
EmployeeID    MgrID    Name
1             null     Joe
2             1        John
3             2        Rob
4             2        Eric

Below is the SQL that allows me to display the hierarchy from top to bottom:

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 am I stuck in that I can't figure out how to request a lower level employee, either Rob or Eric, and return the hierarchy above him from Joe> John> Eric. It seems like it should be easy, but I cannot define it for life.

+5
1

? ?

, , .

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
+8

More articles:


All Articles