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_Parent,e.Firm_Name, e.Rollup_Level, 0 as Depth FROM firm e WHERE Firm_id = 7 UNION All SELECT e2.Firm_ID, e2.Firm_Parent,e2.Firm_Name, e2.Rollup_Level, Depth + 1 FROM firm e2 JOIN Family 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