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;

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!