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
Raw
cycle.sql
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)
ON CONFLICT (comment_id) DO NOTHING
;
WITH pkey_ (comment_id) AS (
VALUES (5)
)
,cycle1_ (comment_id, parent_id) AS (
SELECT comment.comment_id, comment.parent_id
FROM comment
JOIN pkey_ ON pkey_.comment_id = comment.comment_id
UNION
SELECT comment.comment_id, comment.parent_id
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
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment