Hello,

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

Take the following data in EMPLOYEE:

(1,NULL)
(2, 1)
(3, 2)
(4, 1)

And the following in WINNER:

(3)

I want the query to return:

1
2
3

Since 3 is present in WINNER, 3 is included (first condition).
Since 3 is present in WINNER, 2 is included (second condition).
Since 3 is present in WINNER, 1 is included (second condition).

See what I mean? Is this possible using a recursive CTE? I'd rather
not use cursors, etc, unless I really had to. Maybe there is a
completely different way to do this?

I'm using SQL Server 2005.

Thanks.

--
Mike

with CTE(WinnerID, AssociatedWinnerOrParentID)
as (
select EMPLOYEE_ID,EMPLOYEE_ID
from WINNER
union all
select c.WinnerID,e.MANAGER_ID
from CTE c
inner join EMPLOYEE e on e.ID=c.AssociatedWinnerOrParentID
and e.MANAGER_ID is not null)
select WinnerID,
AssociatedWinnerOrParentID
from CTE

Mike (michaelloll (AT) hotmail (DOT) com) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks to all who replied. These solutions worked great. I was able
to incorporate them into my stored procedure and everything is working
fine.

Thanks again!

--
Mike

You might want to look at the Nested Sets model for trees and
hieratchies. You would avoid all the overhead of recursive calls,
which are not cheap.and have much simpler code to maintain.