Recursive CTE Example

  • Comments posted to this topic are about the item Recursive CTE Example

  • Neil,

    Awesome code - thanks! Still trying to get my head around it - lol.

    A suggestion: I was looking for a way to get the manager's name as well as their ID in the results and came up with the following:

    1) Set the big boss' Reports_To_ID = to his Employee_ID, instead of null; i.e. change:

    INSERT INTO @Employees

    SELECT 1,'Roy Hodgson',NULL,'Managing Director'

    to

    INSERT INTO @Employees

    SELECT 1,'Roy Hodgson',1,'Managing Director'

    2) Change the final select statement to:

    SELECT a.Employee_ID ,

    a.Employee_Name ,

    a.Reports_To_ID ,

    b.Employee_Name Manager_Name,

    a.Job_Title

    FROM _hirearchy a JOIN _hirearchy b ON b.Employee_ID = a.Reports_To_ID

    I've attached my code for your / everyone's benefit to see the changes.

    The only problem I see with this is if you change the @BossID to a different value (7248 for Kyle Walker, for example), he doesn't show up in the results as an Employee. Can you think of a way to change your query to return the Manager_Name? That would be awesome!

    Kudos again,

    Mike

  • Crisp. Well written

  • Hello everyone!

    Great post!

    The only way I've found to get the manager name is changing the table "_hirearchy b" for @Employees in the final select with a left join. It also works with the original insert for the ID 1 with Reports_To_ID = NULL.

    I guess this decrease performance or, at least, you lose some of the advantages of using CTE.

    FROM _hirearchy a LEFT OUTER JOIN @Employees b ON b.Employee_ID = a.Reports_To_ID

    Please, tell me if this is too awful :-P.

    Greetings to all!

  • Awesome thanks Mike!!

  • Thanks for the script.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply