SQLize Online / PHPize Online  /  SQLtest Online

# create the table CREATE TABLE orgchart( id INT PRIMARY KEY, name VARCHAR(20), role VARCHAR(20), manager_id INT, FOREIGN KEY (manager_id) REFERENCES orgchart(id)); # insert the rows INSERT INTO orgchart VALUES(1,'Matthew','CEO',NULL), (2,'Caroline','CFO',1),(3,'Tom','CTO',1), (4,'Sam','Treasurer',2),(5,'Ann','Controller',2), (6,'Anthony','Dev Director',3),(7,'Lousie','Sys Admin',3), (8,'Travis','Senior DBA',3),(9,'John','Developer',6), (10,'Jennifer','Developer',6),(11,'Maria','Junior DBA',8); # let's see the table, The CEO has no manager, so the manager_id is set to NULL SELECT * FROM orgchart; # find the reporting chain for all the employees WITH RECURSIVE reporting_chain(id, name, path) AS ( SELECT id, name, CAST(name AS CHAR(100)) FROM orgchart WHERE manager_id IS NULL UNION ALL SELECT oc.id, oc.name, CONCAT(rc.path,' -> ',oc.name) FROM reporting_chain rc JOIN orgchart oc ON rc.id=oc.manager_id) SELECT * FROM reporting_chain;