SQL repeat CTE for multiple values

Question

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

See [SQL Server CTE and recursion example](https://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example). — Tim Biegeleisen, Sep 16 '19 at 07:55
If you want all ID's, why not just remove `where id = 2`? Also, come join the rest of us in 1992 onwards: [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) — Thom A, Sep 16 '19 at 08:00
Larnu: removing 'where id = 2' will not give me the hierarchical relations for each leaf node. — Kristian T, Sep 16 '19 at 08:27

score 0 · Accepted Answer · answered Sep 16 '19 at 09:34

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)

Thx! And i can then add the leaf select as inner select in the in () part — Kristian T, Sep 16 '19 at 10:18

score 0 · Answer 2 · answered Sep 16 '19 at 09:53

Shouldn't this work ?

WITH parents AS (
  SELECT id, name, parent_id,  0 AS relative_depth
  FROM agencies
  where parent_id is null

  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

I use as the seed of the recursive CTE all the roots on your tree (the nodes with no parents), so you should arrive to all the leaves.

SQL repeat CTE for multiple values

2 Answers2