Finding all children for multiple parents in single SQL query

Question

Suppose I have a table with parent-child relationships.

parent  child
1       4
1       5
2       6
3       7
4       8
6       9
7       10
8       11

Now I have a query that returns a list of people (eg. 1 and 2) and I want to find all their children, grandchildren etc. (in this case: 4, 5, 6, 8, 9, 11).

I know I can use common table expressions to search recursively, but I wondered if I could create a SQL statement to find all descendents at once without having to iterate over the input set.

Edit: sorry for not being clear enough. I'm looking for something like:

SELECT {Hierarchical relation} from table where parent in (1,2)

which should result in a single output column with rows for 4, 5, 6, 8, 9, 11.

I'm no longer interested in the relationship in the output, just the complete set of family members for multiple families.

Does the output need to show their relationship (child, grandchild, etc) or that they are simply descended? — UnhandledExcepSean, Nov 20 '11 at 21:18
Could you give us a sample of the expected output in table/row-column format just like how you illustrated the table with the parent-child relationships? — Nonym, Nov 20 '11 at 21:36

dani herrera · Accepted Answer · 2011-11-21T08:25:51.780

19

Here it is

---- PlainTable ----
parent  idElement (child)
Null    1
1       4
1       5
2       6
3       7
4       8
6       9
7       10
8       11

WITH tableR (parent, idElement)
AS
(
-- Anchor member definition
    SELECT e.parent, e.idElement
    FROM PlainTable AS e   
    WHERE parent in (1,2)
    UNION ALL
-- Recursive member definition
    SELECT e.parent, e.idElement
    FROM PlainTable AS e
    INNER JOIN tableR AS d
        ON e.parent = d.idElement
)
-- Statement that executes the CTE
SELECT idElement
FROM tableR  --inner join to plain table by id if needed

edited Nov 21 '11 at 08:25

answered Nov 20 '11 at 22:02

dani herrera

48,760
8
117
177

This CTE does search the hierarchical relationship like I intended, but only for a single input. See my edit in the question. – MvdD Nov 20 '11 at 22:50
First query of CTE looks for root nodes, I assume that root nodes have NULL as parent. Changhe this query for other criteria. – dani herrera Nov 20 '11 at 23:07
Try your query in SQL management studio. It finds all descendents for parent IS NULL, which are (1, 4, 5, 8 and 11) and then returns only 1 from that result set. As I mentioned in the question above, the output should be a column with (1, 2, 4, 5, 6, 8, 9, 11), which are all descendents for both parents 1 and 2. – MvdD Nov 20 '11 at 23:13

MvdD · Answer 2 · 2011-11-21T03:25:50.607

1

Ok, danihp's solution did put me on the right track. This is the solution I came up with:

DECLARE @Input TABLE (
  id int
)

INSERT INTO @Input VALUES (1),(2)

;WITH Relations (parent, child)
AS
(
    SELECT e.parent, e.child
      FROM RelationTable AS e   
      WHERE parent in (SELECT * FROM @Input)
    UNION ALL
      SELECT e.parent, e.child
        FROM RelationTable AS e
        INNER JOIN Relations AS d
          ON e.parent = d.child
)
SELECT child
FROM Relations

It results in a list of child ids (excluding the 2 parent ids like I said earlier in the question): 4,5,6,8,9,11

edited Nov 21 '11 at 03:25

answered Nov 20 '11 at 23:45

MvdD

22,082
8
65
93

oh! Sorry! My mistake, "WHERE idElement in (1,2)" may appear in first query not in last one! I will fix it. – dani herrera Nov 21 '11 at 08:25
This is SQL Gold. Thank you! – Kenneth May 14 '21 at 18:14

score 0 · Answer 3 · answered Aug 12 '15 at 08:43

---- PlainTable ----   
parent  idElement (child_id)
    Null    1
    1       4
    1       5
    2       6
    3       7
    4       8
    6       9
    7       10
    8       11

**Table value function to get Child ids at 4(any) Level of parent in the same table:-**

    FUNCTION fc_get_Child_IDs(Parent)
    DECLARE @tbl TABLE (ID int)
    DECLARE @level int=4
    DECLARE @i int=1
    insert into @tbl  values (Parent)
    while @i < @level
    BEGIN

    INSERT into @tbl 
    select child_id from PlainTable where Parent  in  (select ID from @tbl) and child_id not in (select ID from @tbl) 
     set @i = @i + 1
    END

Brandon Moore · Answer 4 · 2011-11-20T21:38:55.300

SQL Server 2008 has built in features to facilitate hierarchical data: http://msdn.microsoft.com/en-us/magazine/cc794278.aspx

I know I can use common table expressions to search recursively, but I wondered if I could create a SQL statement to find all descendents at once without having to iterate over the input set.

I'm not sure what you mean by that. Most (maybe all?) CTE's can be accomplished through the use of subqueries, but using a subqueries wouldn't be any faster. When you say of you don't want to 'iterate' over the input set it sounds like you're talking about the use of cursors and of course you can do it as a set based operation (using CTEs or subqueries) but there's no way around the recursion.

Edit: I'm sorry, I'm not thinking straight... of course you can't do recursion with normal subqueries but the point still stands that even if you could it wouldn't be faster. If you'd like to see strategies for doing recursion without CTE's then try searches for something like 'recursion sql 2000' since CTE's weren't around back then. Here are some examples: http://www.mssqltips.com/sqlservertip/938/recursive-queries-with-sql-server-2000/. Of course, the answer to your question remains the same though.

score 0 · Answer 5 · answered Nov 20 '11 at 21:35

0

using CTE

Recursive Queries Using Common Table Expressions

http://msdn.microsoft.com/en-us/library/ms186243.aspx

and

http://www.sqlservercentral.com/articles/T-SQL/65540/

be can help you.

answered Nov 20 '11 at 21:35

Reza ArabQaeni

4,848
27
46

Nonym · Answer 6 · 2011-11-21T08:32:17.420

While waiting for an updated post:

SELECT DISTINCT 
  p.parent AS parent
, c.child AS child
, IFNULL(g.child, 'NONE') AS grandchild_of_parent
FROM parent_child as p
LEFT JOIN parent_child AS c ON p.parent = c.parent
LEFT JOIN parent_child AS g ON c.child = g.parent;

Results would look like this:

parent  child   grandchild_of_parent
1       4       8
1       5       NONE
2       6       9
3       7       10
4       8       11
6       9       NONE
7       10      NONE
8       11      NONE

Such a simple-minded-but-probably-harder-to-maintain type of code, but since I'm not familiar with SQL Server 2008's built in features to handle this type of request, I'll just throw a long shot...

EDIT:

Just so you can see results for yourself while you study common table expressions and/or perhaps pivots ... this will get your results, but only up to the great grandchildren of 1 and 2.

-- A. Parents 1 and 2
SELECT DISTINCT p.parent FROM parent_child AS p
WHERE p.parent IN (1,2)
UNION
-- B : Children of A
SELECT DISTINCT p.child FROM parent_child AS p
WHERE p.parent IN (1,2)
UNION
-- C : Children of B, Grandchildren of A
SELECT DISTINCT p.child FROM parent_child AS p
WHERE p.parent IN (
  SELECT DISTINCT p.child FROM parent_child AS p
  WHERE p.parent IN (1,2)
)
UNION
-- D : Children of C, Great-Grandchildren of A
SELECT DISTINCT p.child FROM parent_child AS p
WHERE p.parent IN (
  SELECT DISTINCT p.child FROM parent_child AS p
  WHERE p.parent IN (
    SELECT DISTINCT p.child FROM parent_child AS p
    WHERE p.parent IN (1,2)
  )
)

Again, I strongly suggest you study what the others have been posting.. and look into the links they provided. The inelegant query I provided you is not going to last long->it will absolutely FAIL once you have great-great-grandchildren.

BTW, IFNULL should be ISNULL in the query above for SQL Server. — MvdD, Nov 20 '11 at 23:00

score 0 · Answer 7 · edited May 23 '17 at 10:29

0

Typically I use a Nested Set Model for this and Yes you can have SQL do all the work for you, in fact you have SQL output XML that can be directly attached to .net treeview. Hope this helps

Link: Is there a simple way to query the children of a node?

edited May 23 '17 at 10:29

Community

1
1

answered Nov 20 '11 at 21:48

Darren

329
1
6

Finding all children for multiple parents in single SQL query

7 Answers7

EDIT: