How can I track the ID / ParentID relationship between table rows?

Question

I have a table of type records(ID, ParentID) containing this data:

 ID ParentID 1 null 2 1 3 2 4 2 5 3 6 null 7 6 

If you draw this table in the hierarchy as a family, 1,2,3,4,5 will be related to each other.

I want to find a way to pass an identifier (e.g. 3 ) so that it gives me other family members. I use C # and SQL, or I'll do it - I want to find the following result:

 3 - result 1,2,4,5 2 - result 1,3,4,5 6 - result 7 and so on 

I want to find the parent of the identifier that I pass, grandparents, children and grandchildren (as in my example).

Answer 1

How to create a recursive query in MSSQL 2005?

hope this helps :)

Answer 2

That should do it.

 CREATE TABLE #Test ( ID int, ParentID int ) INSERT #Test VALUES (1, null) INSERT #Test VALUES (2, 1) INSERT #Test VALUES (3, 2) INSERT #Test VALUES (4, 2) INSERT #Test VALUES (5, 3) INSERT #Test VALUES (6, null) INSERT #Test VALUES (7, 6) DECLARE @QueryId int SET @QueryId = 2 -- My parents SELECT [ParentID] FROM #Test WHERE [ID] = @QueryID AND [ParentID] IS NOT NULL UNION -- My parent parents SELECT [ParentID] FROM #Test WHERE [ID] IN (SELECT [ParentID] FROM #Test WHERE [ID] = @QueryID) AND [ParentID] IS NOT NULL UNION -- My parent children (ie my siblings), excluding me SELECT [ID] FROM #Test WHERE [ParentID] IN (SELECT [ParentID] FROM #Test WHERE [ID] = @QueryID) AND [ID] != @QueryID UNION -- My chidren SELECT [ID] FROM #Test WHERE [ParentID] = @QueryId UNION -- My chidren children SELECT [ID] FROM #Test WHERE [ParentID] IN (SELECT [ID] FROM #Test WHERE [ParentID] = @QueryId) DROP TABLE #Test 

Answer 3

You might want to look at the type of Hierarchy that suits your problem, it would seem, well. Although this is only available in SQL Server 2008