Get Children Item based on ParentId....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get Children Item based on ParentId....

Author

Topic

darenkov
Yak Posting Veteran

90 Posts

Posted - 2008-10-27 : 00:38:27

I am looking to create some kind of UDF which will allow me to recursively find all of the children items based on an incoming ParentId Parameter.

As the children items are found I want to perform an update on each one and set the value of a column based on a second parameter in the UDF. ie


Func_UpdateChildrenItem(@OptionItemId, @SectionId)

@OptionItemId int,
@SectionId int

BEGIN
    ....recursively get the children based on the optionItemId (whic is the parentId for the children items)
    .....update each item so that all children have the same section id, ie SectionId = @SectionId
END

some sample data:


OptionItemId ParentId    LookupItemId DataTypeId  SectionId   
------------ ----------- ------------ ----------- ----------- 
1            NULL        1            3           1           
2            1           2            4           1           
3            2           3            1           1           
4            1           4            3           0           
5            2           5            1           1           
6            5           6            1           1           
7            6           5            1           2           
8            1           8            3           0           
9            8           3            1           1           
10           8           6            1           8           
11           2           4            1           1           
12           1           7            3           4           
13           11          1            2           1           
14           2           2            1           1           
15           14          5            2           1           
16           2           3            2           2           
17           16          1            2           2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 00:41:45

Search for recursive CTEs within this forum or books online.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 01:05:05

AS an example

CREATE TABLE #Temp--create sample table to hold data
(
OptionItemId int,
ParentId int,
    LookupItemId int,
 DataTypeId int,
 SectionId int
)

INSERT #Temp--create sample data
SELECT 1  AS OptionItemId,NULL  AS  ParentId,      1  AS LookupItemId,           3  AS DataTypeId,         1  AS SectionId union all         
SELECT 2  ,          1    ,       2   ,         4   ,        1   union all         
SELECT 3  ,          2   ,        3  ,          1  ,         1   union all         
SELECT 4  ,          1   ,        4  ,          3  ,         0  union all          
SELECT 5  ,          2   ,        5  ,          1  ,         1   union all         
SELECT 6  ,          5   ,        6  ,          1  ,         1   union all         
SELECT 7  ,          6   ,        5  ,          1  ,         2   union all         
SELECT 8  ,          1   ,        8  ,          3  ,         0    union all        
SELECT 9   ,         8    ,       3  ,          1 ,          1   union all         
SELECT 10  ,         8   ,        6  ,          1  ,         8   union all         
SELECT 11  ,         2   ,        4  ,          1  ,         1   union all         
SELECT 12  ,         1   ,        7  ,          3  ,         4   union all         
SELECT 13  ,         11   ,       1  ,          2  ,         1   union all         
SELECT 14  ,         2    ,       2  ,          1  ,         1   union all         
SELECT 15  ,         14   ,       5   ,         2  ,         1   union all         
SELECT 16  ,         2    ,       3  ,          2  ,         2    union all        
SELECT 17  ,         16   ,       1   ,         2   ,        2   


DECLARE @OptionItemId int,@sectionid int
SELECT @OptionItemId=8,@sectionid=10
SELECT * FROM  #TEmp--data before update
;With CTE (OptionItemId, ParentId,    LookupItemId, DataTypeId,  SectionId   ) AS
(
SELECT OptionItemId, ParentId,    LookupItemId, DataTypeId,  SectionId
FROM #Temp
WHERE OptionItemId=@OptionItemId
UNION ALL
SELECT t.OptionItemId, t.ParentId,    t.LookupItemId, t.DataTypeId,  t.SectionId
FROM CTE c
JOIN #Temp t
ON t.ParentId=c.OptionItemId)

UPDATE t
SET t.SectionId=@sectionid
FROM #Temp t
JOIN CTE c
ON c.OptionItemId=t.OptionItemId
AND c.OptionItemId<>@OptionItemId

SELECT * FROM  #TEmp--data after update
DROP TABLE #Temp--clean up,remove used temp table

output
------------------------------
before update
-------------------------------
OptionItemId ParentId    LookupItemId DataTypeId  SectionId
------------ ----------- ------------ ----------- -----------
1            NULL        1            3           1
2            1           2            4           1
3            2           3            1           1
4            1           4            3           0
5            2           5            1           1
6            5           6            1           1
7            6           5            1           2
8            1           8            3           0
9            8           3            1           1
10           8           6            1           8
11           2           4            1           1
12           1           7            3           4
13           11          1            2           1
14           2           2            1           1
15           14          5            2           1
16           2           3            2           2
17           16          1            2           2

after update
-----------------------------------------
OptionItemId ParentId    LookupItemId DataTypeId  SectionId
------------ ----------- ------------ ----------- -----------
1            NULL        1            3           1
2            1           2            4           1
3            2           3            1           1
4            1           4            3           0
5            2           5            1           1
6            5           6            1           1
7            6           5            1           2
8            1           8            3           0
9            8           3            1           10
10           8           6            1           10
11           2           4            1           1
12           1           7            3           4
13           11          1            2           1
14           2           2            1           1
15           14          5            2           1
16           2           3            2           2
17           16          1            2           2

darenkov
Yak Posting Veteran

90 Posts

Posted - 2008-10-27 : 01:46:38

Is there another way to do this, perhaps with a User Defined Function using a WHILE loop?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 02:12:11

quote:
Originally posted by darenkov

Is there another way to do this, perhaps with a User Defined Function using a WHILE loop?

you can.but is there any special reason why you dont want to use CTE?
anyways, see method in below link

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109492

darenkov
Yak Posting Veteran

90 Posts

Posted - 2008-10-27 : 02:17:00

i haven't used CTE before so i was just looking for something that i could implement quickly.

i'll have a look at it. thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 02:22:47

quote:
Originally posted by darenkov

i haven't used CTE before so i was just looking for something that i could implement quickly.

i'll have a look at it. thanks

thats why i gave an example also. Try if you can implement it. CTE will make coding lot easier. Anyways, you could also refer the one without use of CTE also.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources