#With clause example

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 10
)
SELECT * FROM cte;

CREATE TABLE employees (
  id         INT PRIMARY KEY NOT NULL,
  name       VARCHAR(100) NOT NULL,
  manager_id INT NULL,
  INDEX (manager_id),
FOREIGN KEY (manager_id) REFERENCES employees (id)
);

INSERT INTO employees VALUES
(333, "Yasmina", NULL),  # Yasmina is the CEO (manager_id is NULL)
(198, "John", 333),      # John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);

SELECT * FROM employees ORDER BY id;

WITH RECURSIVE employee_paths (id, name, path) AS
(
  SELECT id, name, CAST(id AS CHAR(200))
    FROM employees
    WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
    FROM employee_paths AS ep JOIN employees AS e
      ON ep.id = e.manager_id
)
SELECT * FROM employee_paths;

#Base step, we get the base tuple, the CEO
# 333, "Yasmina","333"
#Recursive First Step
# 198, "John", "333,198"
# 692, "Tarek", "333,692"
#Recursive step 2
# 29, "Pedro", "333,192,29"
# 123, "Adil", "333,692,123"
#Recursive step 3
# 4610, "Sarah", "333,192,29,4610"
# 72, "Pierre", "333,192,29,72"

# Please note we compute in a sense
# (R1) union R2=(R1 join Emp) union R3=(R2 join Emp) ..