|
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
Navigation:
[Reply to this message]
|