Help modifying the Microsoft sample CTE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help modifying the Microsoft sample CTE

Author

Topic

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-11 : 15:22:13

I am looking at the CTE example at URL
http://msdn2.microsoft.com/en-us/library/ms186243.a spx

It uses the Adventure Works Cycles company sample data. It is way more complex than I need using multiple tables. But it gives exactly the result I need, a hierarchical list of employees.

The table I am working with has equivalent columns and data as Adventure Works HumanResources.Employee - ManagerID and EmployeeID.

Using only the HumanResources.Employee table I have been trying to write a recursive CTE structure that returns the hierarchical list of employees. But I have note been able to do it.

Can someone show me the correct recursive CTE to return the hierarchical list of employees using only the HumanResources.Employee table?

Thank you

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-10-11 : 16:39:19

Here is the simple version:

WITH DirectReports 
AS
(
-- Anchor member definition
    SELECT 
		e.ManagerID, 
		e.EmployeeID, 
		e.Title, 
        0 AS Level
    FROM 
		HumanResources.Employee AS e
    WHERE 
		ManagerID IS NULL

    UNION ALL

-- Recursive member definition
    SELECT 
		e.ManagerID, 
		e.EmployeeID, 
		e.Title, 
        Level + 1
    FROM 
		HumanResources.Employee AS e
    INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID
)

SELECT *
FROM DirectReports

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-11 : 17:18:48

That is great! Thank you.
I was so close. But the devil is in the details.

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-11 : 18:04:27

I have been playing with this on my data. Works great on all my files that consist of a single tree like Adventure Works. What I found is on tables that have multiple branches Like

ManagerID EmployeeID
Null 100
100 101

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-11 : 18:07:37

I have been playing with this on my data. Works great on all my files that consist of a single tree like Adventure Works. What I found is on tables that have multiple branches Like
this:

ManagerID EmployeeID
Null 100
100 101
100 102
Null 200
200 201
200 202

I don't get an error. But it fails to order correctly. Is there a way to process a table containing multiple branches?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-10-11 : 18:49:31

You should try posting what you tried so we can help you adjust your query, but here is a sample:

DECLARE @Employee TABLE(ManagerID INT, EmployeeID INT)

INSERT @Employee
SELECT Null, 100
UNION ALL SELECT 100, 101
UNION ALL SELECT 100, 102
UNION ALL SELECT Null, 200
UNION ALL SELECT 200, 201
UNION ALL SELECT 200, 202 ;

WITH DirectReports 
AS
(
-- Anchor member definition
    SELECT 
		e.ManagerID, 
		e.EmployeeID, 
        0 AS Level
    FROM 
		@Employee AS e
    WHERE 
		ManagerID IS NULL

    UNION ALL

-- Recursive member definition
    SELECT 
		e.ManagerID, 
		e.EmployeeID, 
        Level + 1
    FROM 
		@Employee AS e
    INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID
)

SELECT 
	*
FROM 
	DirectReports
ORDER BY 
	EmployeeID,
	ManagerID

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-11 : 19:06:31

Again my thanks again for your help. :) Your code is clear and direct I have learned a lot.

I will post my attempts in the future. I just hate to look as green as I am in MS SQL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources