Posted by Mike on 03/02/07 17:41

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

[Back to original message]