CTE get all descendants with parents

Question

Schema:

CREATE TABLE item (
    id        int primary key,
    parent_id int,
    FOREIGN KEY(parent_id) REFERENCES item(id)
);
INSERT INTO item
    (id, parent_id)
VALUES
    (1, NULL),
    (2, 1),
    (3, 1),
    (4, 2),
    (5, 3),
    (6, 3),
    (7, 6)
;

Query:

WITH RECURSIVE descendants(id, parent_id) AS (
    SELECT 1, parent_id FROM item
        UNION ALL
    SELECT item.id, descendants.parent_id
    FROM item, descendants
    WHERE item.parent_id=descendants.id
)
SELECT * FROM descendants;

Fiddle: http://sqlfiddle.com/#!5/27c03/4

Goal: given some parents (eg 2, 3) get all of its descendants.

I came up with the following CTE, but it returns far too many items.

SELECT * FROM descendants WHERE parent_id IN (2, 3);

Should give

id | parent_id
4  | 2
5  | 3
6  | 3
7  | 3

The answer here is close, but my schema varies: https://dba.stackexchange.com/a/94944/253249

Ergest Basha · Answer 1 · 2022-05-30T12:42:23.293

3

Please try and let me know if it helps :

WITH descendants as
(
  SELECT i.id, i.parent_id, CAST(i.id AS varchar) as Level
  FROM item i
  WHERE i.parent_id is null
UNION ALL
SELECT i1.id, i1.parent_id, CAST(i1.id AS varchar) || ', ' || d.Level
  FROM item i1

  INNER JOIN descendants d ON d.id = i1.parent_id
 )
SELECT * 
From descendants
where parent_id in (2,3)

Demo

Reference

edited May 30 '22 at 12:42

answered May 30 '22 at 10:14

Ergest Basha

5,369
3
7
22

score 3 · Answer 2 · answered May 30 '22 at 10:49

For definite base id use

WITH RECURSIVE 
descendants(id, parent_id, most_parent_id) AS (
  SELECT item.id, item.parent_id, item.parent_id
  FROM item 
  WHERE item.parent_id = 1      -- given base id value
  UNION ALL
  SELECT item.id, item.parent_id, descendants.most_parent_id
  FROM descendants
  JOIN item ON descendants.id = item.parent_id
)
SELECT id, parent_id 
FROM descendants
WHERE parent_id <> most_parent_id;

For base id values list (multiple trees or branches) use

WITH RECURSIVE 
base_ids (id) AS (
  SELECT 1
  UNION ALL
  SELECT 3
),
descendants(id, parent_id, most_parent_id) AS (
  SELECT item.id, item.parent_id, item.parent_id
  FROM base_ids
  JOIN item ON base_ids.id = item.parent_id
  UNION ALL
  SELECT item.id, item.parent_id, descendants.most_parent_id
  FROM descendants
  JOIN item ON descendants.id = item.parent_id
)
SELECT * 
FROM descendants
WHERE parent_id <> most_parent_id;

https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=bda692a6854b8af9d53f337e98fd9604

CTE get all descendants with parents

2 Answers2