Posted by Erland Sommarskog on 03/02/07 22:33

Mike (michaelloll@hotmail.com) writes:
> I'm trying to use a recursive CTE to find some data but I am having
> trouble crafting the CTE; maybe what I want isn't possible.
>
> I have the following tables:
>
> EMPLOYEE (int ID, int MANAGER_ID)
> WINNER(int EMPLOYEE_ID)
>
> * The heirarchy in EMPLOYEE may be multiple levels deep.
> * MANAGER_ID is a foreign key to EMPLOYEE.ID
> * WINNER.EMPLOYEE_ID is a foreign key to EMPLOYEE.ID
>
> Basically, I want to generate a list of EMPLOYEE.ID data which
> satisfies the following criteria:
>
> 1) The ID is present in the WINNER table,
> -or-
> 2) A child record of the current record, or a child of a child (ad
> infinitum) is present in the WINNER table

This seems to do what you are asking for:

CREATE TABLE EMPLOYEE (ID int, MANAGER_ID int)
CREATE TABLE WINNER(EMPLOYEE_ID int)

INSERT EMPLOYEE VALUES (1,NULL)
INSERT EMPLOYEE VALUES (2, 1)
INSERT EMPLOYEE VALUES (3, 2)
INSERT EMPLOYEE VALUES (4, 1)

INSERT WINNER VALUES(3)
go
WITH CTE (ID, MANAGER_ID) AS (
SELECT E.ID, E.MANAGER_ID
FROM EMPLOYEE E
JOIN WINNER W ON E.ID = W.EMPLOYEE_ID
UNION ALL
SELECT E.ID, E.MANAGER_ID
FROM EMPLOYEE E
JOIN CTE C ON E.ID = C.MANAGER_ID
)
SELECT ID FROM CTE
go
DROP TABLE EMPLOYEE, WINNER


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]