SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
Create table firm (Firm_ID int, Firm_Name varchar (10),Rollup_Level int,Firm_Parent int); INSERT INTO FIRM (Firm_ID,Firm_Name, Rollup_Level, Firm_Parent)VALUES(1,'Firm1',1,NULL); INSERT INTO FIRM (Firm_ID,Firm_Name, Rollup_Level, Firm_Parent) VALUES(2,'Firm2',2,1); INSERT INTO FIRM (Firm_ID,Firm_Name, Rollup_Level, Firm_Parent) VALUES(3,'Firm3',3,2); INSERT INTO FIRM (Firm_ID,Firm_Name, Rollup_Level, Firm_Parent) VALUES(4,'Firm4',3,2); INSERT INTO FIRM (Firm_ID,Firm_Name, Rollup_Level, Firm_Parent) VALUES(5,'Firm5',4,4); INSERT INTO FIRM (Firm_ID,Firm_Name, Rollup_Level, Firm_Parent) VALUES(6,'Firm6',4,4); INSERT INTO FIRM (Firm_ID,Firm_Name, Rollup_Level, Firm_Parent) VALUES(7,'Firm7',2,1); INSERT INTO FIRM (Firm_ID,Firm_Name, Rollup_Level, Firm_Parent) VALUES(8,'Firm8',3,7); INSERT INTO FIRM (Firm_ID,Firm_Name, Rollup_Level, Firm_Parent) VALUES(9,'Firm9',3,7); INSERT INTO FIRM (Firm_ID,Firm_Name, Rollup_Level, Firm_Parent) VALUES(10,'Firm10',4,9); INSERT INTO FIRM (Firm_ID,Firm_Name, Rollup_Level, Firm_Parent) VALUES(11,'Firm11',5,10); INSERT INTO FIRM (Firm_ID,Firm_Name, Rollup_Level, Firm_Parent) VALUES(12,'Firm12',5,10); INSERT INTO FIRM (Firm_ID,Firm_Name, Rollup_Level, Firm_Parent) VALUES(13,'Firm13',4,9); WITH Family AS ( Select e.Firm_id, e.Firm_name,e.Rollup_Level, e.firm_parent From firm e WHERE Firm_id = 7 UNION ALL Select e2.Firm_id, e2.Firm_name,e2.Rollup_Level From firm e2 JOIN ON Family.Firm_Id = e2.Firm_parent ) Select firm_id, firm_name , rollup_level, firm_parent FROM Family ORDER BY rollup_level, firm_id ASC;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear