SQLize | PHPize | SQLtest

# create the table CREATE TABLE users( id INT PRIMARY KEY, owner VARCHAR(20), address VARCHAR(20) unique, FOREIGN KEY (owner) REFERENCES users(address)); # insert the rows INSERT INTO users VALUES (1, null,'0x1'), (2,'0x1','0x2'), (3,'0x1','0x3'), (4,'0x3','0x4'), (5,'0x2','0x5'), (6,'0x4','0x6'), (7,'0x3','0x7'), (8,'0x1','0x8'), (9,'0x1','0x9'), (10,'0x7','0x10'); # let's see the table, The CEO has no manager, so the manager_id is set to NULL SELECT * FROM users; # find the reporting chain for all the employees WITH RECURSIVE reporting_chain(owner, address, path) AS ( SELECT owner, address, CAST(address AS CHAR(100)) FROM users WHERE owner IS NULL UNION ALL SELECT oc.owner, oc.address, CONCAT(rc.path,' -> ',oc.address) FROM reporting_chain rc JOIN users oc ON rc.address = oc.owner) SELECT * FROM reporting_chain;