Common Table Expressions, Reversed


There was an interesting question left on my last post entitled Hierarchies WITH Common Table Expressions. Basically in this post I showed a couple of ways that you can use SQL Server 2005’s new “Common Table Expressions” (the “WITH” SQL statement) to retrieve a subset of a hierarchy of data. The question concerned inverting how this was done – I had shown how to retrieve all employees that report (directly or indirectly) to one manager – but what if I wanted to retrieve the whole management chain above a specified individual?


Consider the organisation chart below;



In my previous post, I enabled you to specify the IT Director, and get the following results;

















Reporting to IT Director


Head of Operations


Head of Development


Team Leader 1


Team Leader 2


Developer


That is, a whole branch of the organisation. But if instead I wanted to retrieve the management chain for the employee labelled “Developer”, how would I approach this? I have highlighted the individuals I would expect to see in the result set on the organisation chart below;



The key here is to invert the way that you think about the query. We have previously been thinking about retrieving the CEO first, and then joining it to those who report to him. Flipping that concept on its head, lets instead first retrieve the individual who’s management chain we want;


DECLARE @TheEmployee nvarchar(20)


SET @TheEmployee = “Developer”;


 


SELECT


    Id, [Name], ManagerId


FROM


    dbo.Employee


WHERE


    [Name] = @TheEmployee


This is pretty easy. So what do we want next? We want this developer’s boss, but in the SQL we’ve just written we already know this boss’ Identifier from the ManagerId column! So let’s make a leap to using a CTE;


DECLARE @TheEmployee nvarchar(20)


SET @TheEmployee = “Developer”;


 


WITH OrganisationChart (Id, [Name], ManagerId)


AS(


      SELECT


            Id, [Name], ManagerId


      FROM


            dbo.Employee


      WHERE


            [Name] = @TheEmployee


      UNION ALL


      SELECT


            emp.Id, emp.[Name], emp.ManagerId


      FROM


            dbo.Employee emp


      INNER JOIN OrganisationChart ON


             OrganisationChart.ManagerId = emp.Id


)


SELECT * FROM OrganisationChart


This looks like a lot more SQL, but it isn’t really. The first clause of the CTE is the SQL we wrote to retrieve our “Developer”. Then we have our recursive clause that joins this result to any employee that has an “Id” field that matches the “ManagerId” field for our Developer. Of course, this is recursive, so we then get the manager of our manager in the next result... and so on.


And that’s it – we can easily get the whole management chain above any individual using a single SQL statement with a single parameter.


I hope you find that useful - and can I also thank jholovacs for the question, as I think this is a great real world example of the potential of CTEs!

Comments (6)

  1. If you’ve been following my blog you should remember a couple of posts about hierarchical data in SQL

  2. This post by Simon Ince explores the topic of Hierarchies with HierarchyID in SQL 2008. If you’ve been

  3. James says:

    Fantastic got my security working around this principal, thanks for the help

  4. qamar says:

    i have situation. i am working on a MLM website. so the solution i m looking for will take a username as a parameter and give me the username of the people above him which i will save as level in my cte. the next thing i wanna do is i have a cashpoint field in my table so i wanna distribute the problems among the usernames based on the hierarchy level. lets username ‘qamar’ has two parent which are ‘a’ and ‘b’. so qamar is level 3 and ‘a’ and ‘b’ level 2 and 1 respectably. so what i want it to update the cashpoints column for a and b with diff amounts. how can i do that? any suggestions?

  5. channa says:

    Exactly what I was looking for.

    Can anybody throw some light on the performance implications of using CTE ?

Skip to main content