How can I trace ID/ParentID relationships among table rows?

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

ID   ParentID  
1    null  
2    1  
3    2   
4    2  
5    开发者_JS百科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 to pass an ID (like 3) such that it gives me the other family members. I'm using C# and SQL, either will do - I want to find a result like this:

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 ID I pass in, the grandparents, the children, and the grandchildren (as in my example).

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

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