Common Table Expression Issue

I am playing around with Common Table Expressions in SQL Server 2005 and am noticing a strange issue.

SQL Server Version: Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Service Pack 2

Please find a query below that works on AdventureWorks.

Purpose of query

The query starts from the CEO of the company (ManagerID = null) and there is a recursive portion that it uses to find all the direct reports.

What I am seeing

Look at the recursive query where I have made the field, Title, really big and bold. This field always returns, "Chief Executive Officer" - the person at the very top of the food-chain.

What I expect to see

I thought that you should see the title of your immediate supervisor (the person who you directly report to). NOTE: Not everybody reports directly to the CEO.

Query

USE AdventureWorks;

GO

WITH DirectReports (ManagerID, EmployeeID, Title, Level)

AS

(

-- Anchor member definition -- Start from the CEO of the company

SELECT e.ManagerID, e.EmployeeID, e.Title, 0 AS Level

FROM HumanResources.Employee AS e

INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh

ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL

WHERE ManagerID IS NULL

UNION ALL

-- POSSIBLE PROBLEM! with this part of the query.

-- Recursive member definition for getting direct reports

SELECT e.ManagerID, e.EmployeeID, d.Title, Level + 1

FROM HumanResources.Employee AS e

INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh

ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL

INNER JOIN DirectReports AS d

ON e.ManagerID = d.EmployeeID

)

-- Statement that executes the CTE

SELECT ManagerID, EmployeeID, Title, Level

FROM DirectReports

I was able to get the desired results with the following query:

USE AdventureWorks;

GO

WITH DirectReports (ManagerID, EmployeeID, Title, Level)

AS

(

-- Anchor member definition -- Start from the CEO of the company

SELECT e.ManagerID, e.EmployeeID, e.Title, 0 AS Level

FROM HumanResources.Employee AS e

INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh

ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL

WHERE ManagerID IS NULL

UNION ALL

-- Recursive member definition for getting direct reports

SELECT e.ManagerID, e.EmployeeID, e2.Title, Level + 1

FROM HumanResources.Employee AS e

INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh

ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL

INNER JOIN HumanResources.Employee AS e2

ON e.managerId = e2.employeeId

INNER JOIN DirectReports AS d

ON e.ManagerID = d.EmployeeID

)

-- Statement that executes the CTE

SELECT ManagerID, EmployeeID, Title, Level

FROM DirectReports

Can somebody explain why I am not able to get the Title of the Immediate supervisor in the first query? It looks like the Title of the very first row is propagating during recursion. Is this how CTEs are supposed to behave?

Thanks!

Radha Mukkai

The anchor member's title is being propagated to all rows simply because you're not introducing any other Employee title into the two SELECT statements that form the CTE.

During the first recursion you return d.Title which is actually the title belonging to the anchor member. You now have two rows with the Title of the anchor member, the recursion then continues keeping the value of d.Title constant each time.

To keep the CTE simple you could use the code below as an alternative to your second example.

Chris

Code Snippet

WITH DirectReports (ManagerID, EmployeeID, Title, LEVEL)
AS
(
-- Anchor member definition -- Start from the CEO of the company
SELECT e.ManagerID, e.EmployeeID, 0 AS LEVEL
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL

-- Recursive member definition for getting direct reports
SELECT e.ManagerID, e.EmployeeID, LEVEL + 1
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)

-- Statement that executes the CTE
SELECT dr.ManagerID,
dr.EmployeeID,
CASE WHEN dr.ManagerID IS NULL
THEN ( SELECT e2.Title
FROM HumanResources.Employee e2
WHERE e2.EmployeeID = dr.EmployeeID),
ELSE em.Title
END AS Title,
dr.LEVEL
FROM DirectReports dr
LEFT JOIN HumanResources.Employee AS em
ON dr.managerId = em.employeeId

|||Your logic is kinda messed up here.

Let's talk through the pseudo code a little.

1 - Populate DirectReports with the CEO, using "CEO" as Title.
2 - Populate the DirectReports with the people that report to anyone in the DirectReports table, using the Title that's already in there as the new value for Title.
3 - Repeat from 2.

So you see, you're never actually putting any new Titles in there. Your source of the Titles is your existing list of Titles, which is just "CEO".

To solve this, you could store both titles in the CTE, or just fetch the Manager's title after you've created the CTE.

Hope this helps,

Rob|||

Thanks Rob and Chris. I realized my mistake after mentally walking through the recursion tree.

Thanks,

Radha Mukkai