How Can I do this??

Question

I have a table records(ID, ParentID) containg this data

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

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

I want to find a way, where I can pass an ID like 3 it give me the others family members using SQL or using C#

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

I want to find my parent and his parents and childs and my child and his childs
LIKE THE EXAMPLES

Answer 1

http://stackoverflow.com/questions/239275/how-do-i-create-a-recursive-query-in-mssql-2005

hope this helps :)

Answer 2

You may want to take a look at the Hierarchy type which fits your problem seemingly well. Although this is only available with SQL Server 2008

Answer 3

This 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's parents
SELECT [ParentID] FROM #Test WHERE [ID] IN (SELECT [ParentID] FROM #Test WHERE [ID] = @QueryID) AND [ParentID] IS NOT NULL
UNION -- My parent's children (i.e. 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's children
SELECT [ID] FROM #Test WHERE [ParentID] IN (SELECT [ID] FROM #Test WHERE [ParentID] = @QueryId)

DROP TABLE #Test