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
Anonymous
October 31, 2007
PingBack from http://msdnrss.thecoderblogs.com/2007/10/31/common-table-expressions-reversed/Anonymous
October 17, 2008
If you’ve been following my blog you should remember a couple of posts about hierarchical data in SQLAnonymous
November 11, 2008
This post by Simon Ince explores the topic of Hierarchies with HierarchyID in SQL 2008. If you’ve beenAnonymous
March 31, 2009
Fantastic got my security working around this principal, thanks for the helpAnonymous
January 19, 2010
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?Anonymous
February 10, 2011
Exactly what I was looking for. Can anybody throw some light on the performance implications of using CTE ?