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?

lc. · Answer 1 · 2013-09-27T03:01:55.197

16

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

edited Sep 27 '13 at 03:01

answered Sep 27 '13 at 02:50

lc.

113,939
20
158
187

siblings? But he wrote "Getting *all* the **children** of a parent..." How can I get all the sub-tree of a given value (parent) - root of a sub-tree? – candlejack Dec 13 '16 at 03:09
@alessadro and by definition your parent's other children are your siblings? – lc. Dec 13 '16 at 03:13
Siblings are in the same "level", like brother. I'm wrong? – candlejack Dec 13 '16 at 03:16
@alessadro That's right. The sample data also only had one level. If you want the entire tree starting at the given id's parent, take a look at the second half of this answer. – lc. Dec 13 '16 at 03:28
I use phpMyAdmin with MariaDB and it have not the "WITH" function :( – candlejack Dec 13 '16 at 03:29
@Ic. could you tell me the MariaDB equivalent? please. – candlejack Dec 13 '16 at 03:38
Very nice - elegant solution. – zatbusch Mar 09 '17 at 07:18

score 7 · Answer 2 · answered Jan 17 '14 at 16:45

7

SQL Authority has a blog with a very nice explanation of how to perform a Hierarchical Query using a Recursive CTE

http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/

Regards

answered Jan 17 '14 at 16:45

Ricardo Huertas

1,296
1
14
15

score -1 · Answer 3 · answered Sep 27 '13 at 02:40

-1

select child
from my_table T1
where exists (select 1 from my_table T2 where child = @parameter and T1.parent = T2.parent)

answered Sep 27 '13 at 02:40

Vulcronos

3,428
3
16
24

score -2 · Answer 4 · edited Jan 06 '17 at 14:28

-2

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

edited Jan 06 '17 at 14:28

Meyer

1,662
7
21
20

answered Jan 06 '17 at 13:56

user7384296

1

Getting all the children of a parent using MSSQL query

4 Answers4

Linked

Related