SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 All parent and child of ID ?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pushp82
Yak Posting Veteran

81 Posts

Posted - 03/23/2013 :  06:15:00  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 03/23/2013 :  06:21:30  Show Profile  Visit chadmat's Homepage  Reply with Quote
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

81 Posts

Posted - 03/23/2013 :  06:47:58  Show Profile  Reply with Quote
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

Sweden
29908 Posts

Posted - 03/23/2013 :  09:56:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



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

nurunseo
Starting Member

Bangladesh
6 Posts

Posted - 03/24/2013 :  02:00:41  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000