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.
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.
Luckily, SQL makes this really easy. To select multiple columns from a table, simply separate the column names with commas!
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With