Skip to content

Instantly share code, notes, and snippets.

@bokwoon95

bokwoon95/cycle.sql

Last active June 9, 2022 08:04
Show Gist options
  • Save bokwoon95/31b8945f9aba54b6c99508298e5c08d0 to your computer and use it in GitHub Desktop.
Save bokwoon95/31b8945f9aba54b6c99508298e5c08d0 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. bokwoon95 revised this gist Jun 9, 2022. 1 changed file with 8 additions and 8 deletions.
    16 changes: 8 additions & 8 deletions cycle.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -18,25 +18,25 @@ VALUES
    ,(5, 'five', 3)
    ,(6, 'six', 4)
    ,(7, 'seven', 2)
    ON CONFLICT (comment_id) DO NOTHING
    ;

    WITH pkey_ (comment_id) AS (
    SELECT 5
    VALUES (5)
    )
    ,cycle1_ (comment_id, parent_id) AS (
    SELECT comment.comment_id, comment.parent_id
    FROM pkey_
    JOIN comment ON comment.comment_id = pkey_.comment_id
    UNION ALL
    FROM comment
    JOIN pkey_ ON pkey_.comment_id = comment.comment_id
    UNION
    SELECT comment.comment_id, comment.parent_id
    FROM cycle1_
    JOIN comment ON comment.parent_id = cycle1_.comment_id
    WHERE cycle1_.comment_id NOT IN (SELECT comment_id FROM comment)
    FROM comment
    JOIN cycle1_ ON cycle1_.parent_id = comment.comment_id
    )
    SELECT
    comment.comment_id
    ,comment.body
    ,comment.parent_id
    FROM comment
    JOIN cycle1_ ON cycle1_.comment_id = comment.comment_id
    ;
    ;
  2. bokwoon95 created this gist Jun 9, 2022.
    42 changes: 42 additions & 0 deletions cycle.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,42 @@
    DROP TABLE comment;

    CREATE TABLE comment (
    comment_id INTEGER PRIMARY KEY
    ,body TEXT
    ,parent_id INT REFERENCES comment (comment_id)
    );

    DELETE FROM comment;

    INSERT INTO comment
    (comment_id, body, parent_id)
    VALUES
    (1, 'one', 5)
    ,(2, 'two', 1)
    ,(3, 'three', 2)
    ,(4, 'four', 1)
    ,(5, 'five', 3)
    ,(6, 'six', 4)
    ,(7, 'seven', 2)
    ;

    WITH pkey_ (comment_id) AS (
    SELECT 5
    )
    ,cycle1_ (comment_id, parent_id) AS (
    SELECT comment.comment_id, comment.parent_id
    FROM pkey_
    JOIN comment ON comment.comment_id = pkey_.comment_id
    UNION ALL
    SELECT comment.comment_id, comment.parent_id
    FROM cycle1_
    JOIN comment ON comment.parent_id = cycle1_.comment_id
    WHERE cycle1_.comment_id NOT IN (SELECT comment_id FROM comment)
    )
    SELECT
    comment.comment_id
    ,comment.body
    ,comment.parent_id
    FROM comment
    JOIN cycle1_ ON cycle1_.comment_id = comment.comment_id
    ;