none
CTE and getting higher level parents RRS feed

  • Question

  • OK I have a parent child relationship set up in my database

    EmployeeId
    EmployeeName
    EmployeeParentId
    EmployeeType

    This structure is several levels deep, and I need to know how to get the Employee's Manager name several levels up the tree.

    Say I have some employee types of

    Company Head
    Division Head
    Department Head
    Team Lead
    Team Member

    I know the Employee of the Team Member and I want to get the name for his Division head, but may not know how many levels up the tree that may be.  I have a UDF set up using CTE that will tell me what level the employee is, I just need to figure out a way to walk back up the tree.

    Thanks for any help.

     

    Ross

    Thursday, March 25, 2010 4:27 PM

Answers

  • declare @Type varchar(40) = 'Division Head'
    declare @Name varchar(100) = 'Louie McDuck'
    
    
    -- Anchor definition
    ;with cte as (select E.EmployeeID, E.EmployeeName, 
    E.EmployeeType, E.EmployeeParentId as HeadID, 
    Case when E.EmployeeType = @Type then E.EmployeeName end as [Division Head], 
    0 as Level, P.EmployeeName as HeadName, P.EmployeeType as HeadType 
    from EmployeeTree E LEFT join EmployeeTree P on E.EmployeeParentId = P.EmployeeId where P.EmployeeId IS NULL
    union all 
    -- recursive part
    select E.EmployeeID, E.EmployeeName, E.EmployeeType, E.EmployeeParentId as HeadID, 
    Case when P.EmployeeType = @Type then P.EmployeeName else [Division Head] end as [Division Head] ,
    Level + 1 as Level, P.EmployeeName as HeadName, P.EmployeeType as HeadType
    from EmployeeTree E inner join cte P on E.EmployeeParentId  = P.EmployeeId)
    
    select * from cte  where EmployeeName = @Name

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by rlouch Thursday, March 25, 2010 10:10 PM
    Thursday, March 25, 2010 5:22 PM
    Moderator

All replies

  • Can you post create table statement and couple of inserts and the desired output. This would be easier.

    In the meantime, you may check CTE and hierarchical queries


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, March 25, 2010 4:35 PM
    Moderator
  • Sure, here is some sample data

     

     

    CREATE TABLE EmployeeTree(
    	EmployeeId int,
            EmployeeName nvarchar(100),
            EmployeeType nvarchar(40),
    	EmployeeParentId int 
    )
    
    INSERT INTO EmployeeTree (EmployeeId, EmployeeName, EmployeeType, EmployeeParentId)
    VALUES (1, 'Mickey Mouse', 'CEO', NULL)
    
    INSERT INTO EmployeeTree (EmployeeId, EmployeeName, EmployeeType, EmployeeParentId)
    VALUES (2, 'Minnie Mouse', 'President', 1)
    
    INSERT INTO EmployeeTree (EmployeeId, EmployeeName, EmployeeType, EmployeeParentId)
    VALUES (3, 'Donald Duck', 'Division Head', 2)
    
    INSERT INTO EmployeeTree (EmployeeId, EmployeeName, EmployeeType, EmployeeParentId)
    VALUES (4, 'Daffy Duck', 'Division Head', 2)
    
    INSERT INTO EmployeeTree (EmployeeId, EmployeeName, EmployeeType, EmployeeParentId)
    VALUES (5, 'Scrooge McDuck', 'Department Head', 3)
    
    INSERT INTO EmployeeTree (EmployeeId, EmployeeName, EmployeeType, EmployeeParentId)
    VALUES (6, 'Huey McDuck', 'Team Lead', 5)
    
    INSERT INTO EmployeeTree (EmployeeId, EmployeeName, EmployeeType, EmployeeParentId)
    VALUES (7, 'Dewey McDuck', 'Team Lead', 5)
    
    INSERT INTO EmployeeTree (EmployeeId, EmployeeName, EmployeeType, EmployeeParentId)
    VALUES (8, 'Louie McDuck', 'Team Member', 7)
    
    
    

    Then I would like to be able to find who is the division head for Louie McDuck knowing his EmployeeId as well as the EmployeeType 'Division Head' 

    The desired output would be

    EmployeeName   DivisionHead
    ------------------   ----------------
    Louie McDuck      Donald Duck

     

    Thursday, March 25, 2010 4:50 PM
  • declare @Type varchar(40) = 'Division Head'
    declare @Name varchar(100) = 'Louie McDuck'
    
    
    -- Anchor definition
    ;with cte as (select E.EmployeeID, E.EmployeeName, 
    E.EmployeeType, E.EmployeeParentId as HeadID, 
    Case when E.EmployeeType = @Type then E.EmployeeName end as [Division Head], 
    0 as Level, P.EmployeeName as HeadName, P.EmployeeType as HeadType 
    from EmployeeTree E LEFT join EmployeeTree P on E.EmployeeParentId = P.EmployeeId where P.EmployeeId IS NULL
    union all 
    -- recursive part
    select E.EmployeeID, E.EmployeeName, E.EmployeeType, E.EmployeeParentId as HeadID, 
    Case when P.EmployeeType = @Type then P.EmployeeName else [Division Head] end as [Division Head] ,
    Level + 1 as Level, P.EmployeeName as HeadName, P.EmployeeType as HeadType
    from EmployeeTree E inner join cte P on E.EmployeeParentId  = P.EmployeeId)
    
    select * from cte  where EmployeeName = @Name

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by rlouch Thursday, March 25, 2010 10:10 PM
    Thursday, March 25, 2010 5:22 PM
    Moderator
  • Thanks it worked like a charm.  I only wish I worked at Disney, it was the easiest way to replicate my data structure without totally confusing you in the process.
    Thursday, March 25, 2010 10:10 PM