Is it possible to solve this Issue using SQL 2

[Shreyas]

Hi All,

Here is the description of the problem :

Child Parent
16 9
17 9
18 16
19 17


Now the deal is I'll get on parent key from which I need to pull out all its children and obviously I do not know many children one parent can have. Can somebody tell me a best way to get all the children of a given parent id. Also suggest me if it is doable using a dynamic query.

From the above sample it goes like this :
9 -> 16 -> 18
17 -> 19 ...
I need the output as
9
16
17
18
19
...

Thanks

Draj

 

[tlbroadbent]

There is no "simple" recursion in SQL. Here are some links that you may find helpful.

http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/recursion.asp

http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_14_5yk3.asp

http://www.sqlteam.com/ItemPrint.asp?ItemID=1353

http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci537290,00.html

http://www.intelligententerprise.com/001020/celko.shtml

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Shreyas]

It works, I believe recursion is the only feasible solution for this. Thanks a TON Mr. Terry L broadbent.


alter proc tmp_GetAllChildrenFromParent @iPrmShowCaseCategoryID int
as
declare @iFirstTimeFlag int
select @iFirstTimeFlag = 0
set nocount on
DECLARE Product_Cursor CURSOR LOCAL FOR
SELECT showcase_category_id
FROM dbo.showcase_category
WHERE parent_key=@iPrmShowCaseCategoryID
declare @iShowcase_id int
OPEN Product_Cursor
select @iPrmShowCaseCategoryID
FETCH NEXT FROM Product_Cursor into @iShowcase_id
WHILE @@FETCH_STATUS = 0
BEGIN
exec tmp_GetAllChildrenFromParent @iShowcase_id
FETCH NEXT FROM Product_Cursor into @iShowcase_id
END
CLOSE Product_Cursor
DEALLOCATE Product_Cursor
go