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.
| Author |
Topic |
|
craigmacca
Posting Yak Master
142 Posts |
 Posted - 2012-06-30 : 04:53:34
|
| Hi i need to loop through all child nodes but not sure how to do this in SQLi have a table with the followingId ParentId1 02 03 14 15 36 3i need to select all items below the parentid that is passed in, i am sure there is a simple way todo this? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-06-30 : 13:41:55
|
| What is the output you are trying to get after looping through all the child nodes? If you are on SQL Server 2005 or later, recursive CTE's probably would be the easiest and best approach. There is documentation and a very good example here |
 |
|
|
craigmacca
Posting Yak Master
142 Posts |
Posted - 2012-06-30 : 17:28:07
|
| Hi, when looping through them I will be updating or inserting into a link table |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-01 : 00:50:53
|
| seehttp://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-01 : 10:37:39
|
quote:
Originally posted by craigmacca
Hi, when looping through them I will be updating or inserting into a link table
DECLARE @id INT;SET @id = 3;;WITH cte AS( SELECT id, parentid FROM YourTable WHERE id = @id UNION ALL SELECT y.id,y.parentid FROM YourTable y INNER JOIN cte c ON c.id = y.parentid)SELECT * FROM cte;-- remove the last select and insert into your new table here. |
|
|
|
|
|
|
recursive loop