Category Archives: Sql Server 2005

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