recursive select of child-/parent (this only works in postgresql, not mysql) #sql

V1.MD

without path

create table

CREATE TABLE _test(id SERIAL PRIMARY KEY, item_id INT NOT NULL, parent_id INT);
INSERT INTO _test(item_id, parent_id) VALUES (1, null), (2, 1), (3, 1), (3, 5), (4, 3), (5, 5);

get children recursively

WITH RECURSIVE rec AS (
  SELECT id, item_id, parent_id, 1 AS level FROM _test
  WHERE parent_id = 1 -- START
  UNION ALL SELECT c.id, c.item_id, c.parent_id, (p.level+1) FROM _test c JOIN rec p ON c.parent_id = p.item_id
) SELECT DISTINCT item_id, level FROM rec ORDER BY level ASC;

get parents recursively

WITH RECURSIVE rec AS (
  SELECT id, item_id, parent_id, 1 AS level FROM _test
  WHERE item_id = 2 -- START
  UNION ALL SELECT c.id, c.item_id, c.parent_id, (p.level+1) FROM _test c JOIN rec p ON c.item_id = p.parent_id
) SELECT DISTINCT parent_id, level FROM rec WHERE parent_id IS NOT NULL ORDER BY level ASC;

without path (multiple parents, multiple tables)

create table

CREATE TABLE humans(id SERIAL PRIMARY KEY);
CREATE TABLE births(id SERIAL PRIMARY KEY, mother_id INT, father_id INT);
CREATE TABLE humans_births(id SERIAL PRIMARY KEY, human_id INT NOT NULL, birth_id INT NOT NULL);
INSERT INTO humans(id) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
INSERT INTO births(id, father_id, mother_id) VALUES (1,10,9),(2,8,7),(3,6,5),(4,20,19),(5,18,17),(6,16,15);
INSERT INTO humans_births(id, human_id, birth_id) VALUES (1,1,1),(2,2,2),(3,3,3),(4,4,1),(5,10,4),(6,12,5),(7,13,6),(8,14,4);

get parents recursively

WITH RECURSIVE rec AS (
  SELECT d.id, l.father_id, l.mother_id, 1 AS level
        FROM humans as d JOIN humans_births as ld ON ld.human_id = d.id JOIN births as l ON l.id = ld.birth_id
  WHERE d.id = 1 -- START        
  UNION ALL SELECT d.id, l.father_id, l.mother_id, (p.level+1)
        FROM humans as d JOIN humans_births as ld ON ld.human_id = d.id JOIN births as l ON l.id = ld.birth_id
    JOIN rec p ON (d.id = p.father_id OR d.id = p.mother_id) WHERE p.level+1 <= 5
) SELECT father_id, mother_id FROM rec;

with path

create table

CREATE TABLE rel(child integer, parent integer);
INSERT INTO rel(child, parent) VALUES (1,NULL), (2,1), (3,1), (4,3), (5,2), (6,4), (7,2), (8,7), (9,8);

get parents recursively

WITH RECURSIVE t(child, parentlist) AS (
  SELECT child , ARRAY[]::INTEGER[] FROM rel WHERE parent IS NULL
  UNION
  SELECT rel.child, rel.parent || t.parentlist 
    FROM rel 
    JOIN t ON rel.parent = t.child
) SELECT * FROM t;