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
 General SQL Server Forums
 New to SQL Server Programming
 All parent and child of ID ?

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_ID
1 0
2 0
3 0
4 1
5 4
6 5
7 2
8 3
9 7

I want to get all parent and child of given ID. How?
Like the given ID id is 4
Then
1
4
5
6

Please 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
Go to Top of Page

pushp82
Yak Posting Veteran

83 Posts

Posted - 2013-03-23 : 06:47:58
hey chad that's the problem actually:
1 is parent
4 is under 1
5 is under 4
6 is under 5
So I want complete chain of any given ID.

Go to Top of Page

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 cteUp

UNION ALL

SELECT *
FROM cteDown[/code]


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

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]
Go to Top of Page
   

- Advertisement -