CTE get all descendants with parents

[tahishae]

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

[baileigh]

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