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 |
