Common Table Expression Recursion

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)

Common Table Expression Recursion

Author

Topic

jholovacs
Posting Yak Master

163 Posts

Posted - 2007-10-31 : 09:42:49

Every article I've seen about CTE talks about the wonderful recursive capabilities this feature has. The commonly cited example is finding a list of all employees that ultimately report to a single senior manager.

However, I have seen nothing on how to do recursion from the other end.

Say I have a table:


CREATE TABLE foo
	(
	EmpID INT NOT NULL,
	EmpName VARCHAR(20) NOT NULL,
	ManagerEmpID INT NULL
	)

And I want to find out the chain of command for Peon A (we'll call him Joe WorkerBee from here on).

I don't want to know about all of Mr. WorkerBee's coworkers. There may be millions of them and I certainly don't want some sort of "master list" generated when all I need is one small subset.

What I want is, for a given employee ID, show me the management chain, in order, up to the top where the top guy does not have a manager.

I can't seem to figure out how this would be written as a recursive query. Can anyone help me?

___________________________
Geek At Large

jholovacs
Posting Yak Master

163 Posts

Posted - 2007-10-31 : 10:30:21

OK, never mind, I think I figured it out.

 

DECLARE @EmpID int;
SET @EmpID = (your employee's id) ;
WITH Managers(EmpID, ManagerEmpID, recursion_level) as
(
SELECT
   EmpID,
   managerEmpID,
   0
FROM foo f
WHERE EmpID = @EmpID
-----------------------
UNION ALL
-----------------------
SELECT
   f1.EmpID,
   f1.ManagerID
   m.recursion_level +1
FROM foo f1
INNER JOIN Managers m
   ON m.ManagerEmpID = f1.EmpID
)
SELECT
   EmpID,
   ManagerEmpID,
   recursion_level
FROM Managers m1

___________________________
Geek At Large

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources