My problem is that I have this functional CTE statement in SQL. It works like a charm to find the deep hierarchy for my agency with id = 13.
Now what I want it to do is give me a result for multiple Ids (more specific all my leaves in the tree).
The statement for finding all the leaves are stated below the CTE.
Can this be done? Or do I need to run the query from code for each ID?
WITH parents AS (
SELECT id, name, parent_id, 0 AS relative_depth
FROM agencies
where id = 13
UNION ALL
SELECT cat.id, cat.name, cat.parent_id, p.relative_depth - 1
FROM agencies cat, parents p
WHERE cat.id = p.parent_id
)
SELECT parents.id, parents.name as agency, parents.relative_depth FROM parents
Example Output with id=13:
id name relative_depth
13 D 0
12 C -1
8 B -2
2 A -3
Example Output with id=11:
id name relative_depth
11 E 0
2 A -1
So for the 4 leaves that i have from the below statement...
Leaves statement:
SELECT id
FROM agencies t
WHERE NOT EXISTS (SELECT 1
FROM agencies
WHERE parent_id = t.id)
I want to get a result like this:
leaf id name relative_depth
13 13 D 0
13 12 C -1
13 8 B -2
13 2 A -3
11 11 E 0
11 2 A -2
. . . .
. . . .
/Kind regards
Does something like this work for you, where you would hold the top-level id in the leaf field, and then specify the leaf criteria in your final SELECT:
WITH parents AS (
SELECT id leaf, id, name, parent_id, 0 AS relative_depth
FROM agencies
UNION ALL
SELECT p.leaf, cat.id, cat.name, cat.parent_id, p.relative_depth - 1
FROM agencies cat, parents p
WHERE cat.id = p.parent_id
)
SELECT leaf, parents.id, parents.name as agency, parents.relative_depth
FROM parents
WHERE leaf IN (11, 13)