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 |
pushp82
Yak Posting Veteran
83 Posts |
Posted - 2013-03-23 : 06:15:00
|
Hi All,My table has parent and child relationship Like:ID Sub_ID1 02 03 04 15 46 57 28 39 7I want to get all parent and child of given ID. How?Like the given ID id is 4Then 1456Please help ASAP if possible! |
|
chadmat
The Chadinator
1974 Posts |
Posted - 2013-03-23 : 06:21:30
|
I don't get it. I thought I did, but why is 6 in the result set?-Chad |
|
|
pushp82
Yak Posting Veteran
83 Posts |
Posted - 2013-03-23 : 06:47:58
|
hey chad that's the problem actually:1 is parent4 is under 15 is under 46 is under 5So I want complete chain of any given ID. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-03-23 : 09:56:07
|
[code]DECLARE @Sample TABLE ( ID INT, SubID INT );INSERT @Sample ( ID, SubID )VALUES (1, 0), (2, 0), (3, 0), (4, 1), (5, 4), (6, 5), (7, 2), (8, 3), (9, 7);DECLARE @ID INT = 4;WITH cteUp(ID)AS ( SELECT ID FROM @Sample WHERE ID = @ID UNION ALL SELECT s.SubID FROM @Sample AS s INNER JOIN cteUp AS u ON u.ID = s.ID WHERE s.SubID > 0), cteDown(ID)AS ( SELECT ID FROM @Sample WHERE SubID = @ID UNION ALL SELECT s.ID FROM @Sample AS s INNER JOIN cteDown AS u ON u.ID = s.SubID)SELECT *FROM cteUpUNION ALLSELECT *FROM cteDown[/code] N 56°04'39.26"E 12°55'05.63" |
|
|
nurunseo
Starting Member
6 Posts |
Posted - 2013-03-24 : 02:00:41
|
hi guys! Well. I can say that I am a child of my parent. Thanks for nice shearing..............[url=http://paneuromix.com/en]NZT[/url][url=http://paneuromix.com/nootropics.html]Nootropics[/url] |
|
|
|
|
|
|
|