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:

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:

  • How to select date without time in SQL
  • MDX Columns as rows
  • Storing the url in SQLSERVER 2005 using C# code(data sets)
  • How to create dynamic SQL queries inside CURSOR
  • How to create a link server: target is SQL server 2000 on a SQL server 2008
  • C# SQLConnection pooling
  • 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.

  • SQL Server CTE referred in self joins slow
  • In SQL, how will you distribute the deduction to each row based on each row's capacity?
  • T-SQL CTE apply percentage formula and sum up values for each date
  • Find element in hierarchy
  • Comments Parent-Child query with indentation
  • Does the number of columns used for a CTE affects the performance of the query?
  • 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 ,
               FROM     Employees p
               WHERE    p.NAME = 'Eric'
               UNION ALL
               SELECT   c.EmployeeID ,
                        c.MgrID ,
               FROM     employeeMaster cte
                        INNER JOIN Employees c ON c.EmployeeID = cte.MgrID
    SELECT  *
    FROM    employeeMaster m
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.