SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
# create the table CREATE TABLE users( id INT PRIMARY KEY, owner VARCHAR(20), address VARCHAR(20)); # insert the rows INSERT INTO users VALUES (1,'','0x1'), (2,'0x1','0x2'), (3,'0x1','0x3'), (4,'0x3','0x4'), (5,'0x2','0x5'), (6,'0x4','0x6'), (7,'0x3','0x7'), (8,'','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;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear