Getting all the children of a parent using MSSQL query

Question

I have the following data in my database:

Parent      Child
101         102
101         103
101         104
101         105
101         106

My parameter is 106. And using the parameter I want to retrieve all the other children under its parent which is 101. I tried using the recursive method but it didn't work given the following data. Is there another way to formulate a query?

Answer 1

Assuming you want to get siblings of the value @p0, you can use a simple self-join:

SELECT p.Child
FROM Table1 c
INNER JOIN Table1 p ON c.Parent = p.Parent
WHERE c.Child = @p0
AND p.Child <> @p0

The not-equal clause here makes sure you get siblings not including the value you searched for. Remove it as necessary.

SQL Fiddle example

---

Since you mention recursion though, perhaps you want the entire tree starting at the parent of the value @p0. In which case, you can use a recursive CTE:

WITH parent AS (
    SELECT Parent
    FROM Table1
    WHERE Child = @p0
), tree AS (
    SELECT x.Parent, x.Child
    FROM Table1 x
    INNER JOIN parent ON x.Parent = parent.Parent
    UNION ALL
    SELECT y.Parent, y.Child
    FROM Table1 y
    INNER JOIN tree t ON y.Parent = t.Child
)
SELECT Parent, Child
FROM tree

SQL Fiddle examples using your data and with additional data to demonstrate the recursive CTE