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!