Example source: https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive-hierarchy-traversal
SQL
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);
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 ORDER BY path;
Output
+------+---------+-----------------+
| id | name | path |
+------+---------+-----------------+
| 333 | Yasmina | 333 |
| 198 | John | 333,198 |
| 29 | Pedro | 333,198,29 |
| 4610 | Sarah | 333,198,29,4610 |
| 72 | Pierre | 333,198,29,72 |
| 692 | Tarek | 333,692 |
| 123 | Adil | 333,692,123 |
+------+---------+-----------------+
Cypher
CREATE (yasmina:Employee { name: "Yasmina", id: 333 })
CREATE (john:Employee { name: "John", id: 198 })
CREATE (tarek:Employee { name: "Tarek", id: 692 })
CREATE (pedro:Employee { name: "Pedro", id: 29 })
CREATE (sarah:Employee { name: "Sarah", id: 4610 })
CREATE (pierre:Employee { name: "Pierre", id: 72 })
CREATE (adil:Employee { name: "Adil", id: 123 })
CREATE
(john)-[:REPORTS_TO]->(yasmina),
(tarek)-[:REPORTS_TO]->(yasmina),
(pedro)-[:REPORTS_TO]->(john),
(sarah)-[:REPORTS_TO]->(pedro),
(pierre)-[:REPORTS_TO]->(pedro),
(adil)-[:REPORTS_TO]->(tarek)
MATCH p=(e:Employee)-[:REPORTS_TO*]->(yasmina:Employee { id: 333 })
RETURN p