recursion.sql

-- If you know how to convert recursive code to iterative code with its own stack,
-- then you understand recursion and can answer simple interview questions about it.

-- mysql 8 and above
CREATE TABLE nodes (
  node INT NOT NULL,
  parent INT,
  -- add any other columns used in the query here
  PRIMARY KEY (node),
  FOREIGN KEY (parent) REFERENCES nodes(node)
);

INSERT INTO `test1`.`nodes` (`node`, `parent`) VALUES (1, NULL);
INSERT INTO `test1`.`nodes` (`node`, `parent`) VALUES (2, 1);
INSERT INTO `test1`.`nodes` (`node`, `parent`) VALUES (3, 1);
INSERT INTO `test1`.`nodes` (`node`, `parent`) VALUES (4, 2);
INSERT INTO `test1`.`nodes` (`node`, `parent`) VALUES (5, 3);
INSERT INTO `test1`.`nodes` (`node`, `parent`) VALUES (6, 2);
INSERT INTO `test1`.`nodes` (`node`, `parent`) VALUES (7, 3);
INSERT INTO `test1`.`nodes` (`node`, `parent`) VALUES (8, 4);

WITH RECURSIVE node_rec AS (
  (SELECT 1 AS depth, CONCAT('/', node) AS path, nodes.node, nodes.parent
    FROM   nodes
    WHERE  parent IS NULL
    LIMIT  10
  )
	
    UNION ALL
  
		SELECT r.depth + 1, CONCAT(r.path, '/', n.node), n.*
    FROM   node_rec r 
    JOIN   nodes    n ON n.parent = r.node
    WHERE  r.depth < 4
)
SELECT *
FROM   node_rec
ORDER  BY path;

-- others
   WITH RECURSIVE node_rec AS (
    (SELECT 1 AS depth, ARRAY[node] AS path, *
      FROM  nodes
      WHERE  parent IS NULL
      LIMIT  10
    )   
      UNION ALL
      SELECT r.depth + 1, r.path || n.node, n.*
      FROM  node_rec r 
      JOIN  nodes   n ON n.parent = r.node
      WHERE  r.depth < 4
   )
   SELECT *
   FROM  node_rec
   ORDER  BY path;