Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Finding all children for multiple parents in single SQL query

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.

like image 992
MvdD Avatar asked Nov 20 '11 21:11

MvdD


People also ask

How do I get all parent children in SQL?

level + 1 FROM pc a JOIN cte c ON a. parent = c. child ) SELECT distinct parent, child , level FROM cte order by level, parent; This will give you all descendants and the level.

How do I SELECT multiple names in SQL?

Luckily, SQL makes this really easy. To select multiple columns from a table, simply separate the column names with commas!


1 Answers

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
like image 90
dani herrera Avatar answered Sep 30 '22 05:09

dani herrera