Recursive Queries in SQL Server 2005
For building recursive queries In SQL Server 2005 we have to use a new functionality called Common Table Expressions.
Let's see how we can retrieve all the employees that report to a manager (ManagerID = 3) in the AdventureWorks database sample:
WITH CTE (EmployeeID, FullName, Title, ManagerID) AS
(
SELECT E.EmployeeID, C.FirstName + ' ' + C.LastName FullName, E.Title, E.ManagerID
FROM HumanResources.Employee E
INNER JOIN Person.Contact C ON E.ContactID = C.ContactID
WHERE E.EmployeeID = 3
UNION ALL
SELECT E.EmployeeID, C.FirstName + ' ' + C.LastName FullName, E.Title, E.ManagerID
FROM HumanResources.Employee E
INNER JOIN Person.Contact C ON E.ContactID = C.ContactID
INNER JOIN CTE ON E.ManagerID = CTE.EmployeeID
)
SELECT * FROM CTE
This query returns the following results:
EmployeeID | Full Name | Title | ManagerID |
3 | Roberto Tamburello | Engineering Manager | 12 |
4 | Rob Walters | Senior Tool Designer | 3 |
9 | Gail Erickson | Design Engineer | 3 |
11 | Jossef Goldberg | Design Engineer | 3 |
158 | Dylan Miller | Research and Development Manager | 3 |
263 | Ovidiu Cracium | Senior Tool Designer | 3 |
267 | Michael Sullivan | Senior Design Engineer | 3 |
270 | Sharon Salavaria | Design Engineer | 3 |
5 | Thierry D'Hers | Tool Designer | 263 |
265 | Janice Galvin | Tool Designer | 263 |
79 | Diane Margheim | Research and Development Engineer | 158 |
114 | Gigi Matthew | Research and Development Engineer | 158 |
217 | Michael Raheem | Research and Development Manager | 158 |
If we don't want to return the manager we change the WHERE clause:
WITH CTE (EmployeeID, FullName, Title, ManagerID) AS
(
SELECT E.EmployeeID, C.FirstName + ' ' + C.LastName FullName, E.Title, E.ManagerID
FROM HumanResources.Employee E
INNER JOIN Person.Contact C ON E.ContactID = C.ContactID
WHERE E.ManagerID = 3
UNION ALL
SELECT E.EmployeeID, C.FirstName + ' ' + C.LastName FullName, E.Title, E.ManagerID
FROM HumanResources.Employee E
INNER JOIN Person.Contact C ON E.ContactID = C.ContactID
INNER JOIN CTE ON E.ManagerID = CTE.EmployeeID
)
SELECT * FROM CTE
And the results are:
EmployeeID | Full Name | Title | ManagerID |
4 | Rob Walters | Senior Tool Designer | 3 |
9 | Gail Erickson | Design Engineer | 3 |
11 | Jossef Goldberg | Design Engineer | 3 |
158 | Dylan Miller | Research and Development Manager | 3 |
263 | Ovidiu Cracium | Senior Tool Designer | 3 |
267 | Michael Sullivan | Senior Design Engineer | 3 |
270 | Sharon Salavaria | Design Engineer | 3 |
5 | Thierry D'Hers | Tool Designer | 263 |
265 | Janice Galvin | Tool Designer | 263 |
79 | Diane Margheim | Research and Development Engineer | 158 |
114 | Gigi Matthew | Research and Development Engineer | 158 |
217 | Michael Raheem | Research and Development Manager | 158 |
If we want to get all the employees that an employee reports to, we just have to change the INNER JOIN order:
WITH CTE (EmployeeID, FullName, Title, ManagerID) AS
(
SELECT E.EmployeeID, C.FirstName + ' ' + C.LastName FullName, E.Title, E.ManagerID
FROM HumanResources.Employee E
INNER JOIN Person.Contact C ON E.ContactID = C.ContactID
WHERE E.EmployeeID = 3
UNION ALL
SELECT E.EmployeeID, C.FirstName + ' ' + C.LastName FullName, E.Title, E.ManagerID
FROM HumanResources.Employee E
INNER JOIN Person.Contact C ON E.ContactID = C.ContactID
INNER JOIN CTE ON E.EmployeeID = CTE.ManagerID
)
SELECT * FROM CTE
And the employees that the employee (EmployeeID = 3) reports to are:
EmployeeID | Full Name | Title | ManagerID |
3 | Roberto Tamburello | Engineering Manager | 12 |
12 | Terri Duffy | Vice President of Engineering | 109 |
109 | Ken Sánchez | Chief Executive Officer | NULL |