You are here: Recursive CTE Question « MsSQL Server « IT news, forums, messages
Recursive CTE Question

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]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация