3

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

2 Answers 2

3

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

3
  • Thanks. Definite base id is good enough. First solution does't help much, as I need a way to get row id=7 when I ask for all descendants of id=3. Second solution also isn't perfect, since I have to look for id=3 in parent_id and most_parent_id. However if the results don't have any other issues then that's acceptable.
    – mustafa.0x
    Commented May 30, 2022 at 12:47
  • @mustafa.0x Simply remove WHERE condition if you need to see both specified id and its descendants.
    – Akina
    Commented May 30, 2022 at 13:15
  • Sorry, not sure I understood, could you share a fiddle?
    – mustafa.0x
    Commented May 30, 2022 at 15:18
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

2
  • Thanks. row id=7 is missing.
    – mustafa.0x
    Commented May 30, 2022 at 12:41
  • row id=7 is a child of id=6, not id=3.
    – mustafa.0x
    Commented May 30, 2022 at 12:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.