CTE get all descendants with parents



  • 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



  • 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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2