clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 756688 fiddles created (13649 in the last week).

CREATE TABLE dept( DEPT_ID INTEGER NOT NULL PRIMARY KEY ,DEPT_NAME VARCHAR(15) NOT NULL ,DEPT_PARENT INTEGER );
 hidden batch(es)


begin INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5500,'World',NULL); INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5510,'Region 1',5500); INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5511,'Cell 1 Region 1',5510); INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5512,'Cell 2 Region 1',5510); INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5513,'Cell 3 Region 1',5510); INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5514,'Cell 4 Region 1',5510); INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5515,'Cell 5 Region 1',5510); INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5520,'Region 2',5500); INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5521,'Cell 1 Region 2',5520); INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5522,'Cell 2 Region 2',5520); INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5530,'Region 3',5500); INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5531,'Cell 1 Region 3',5530); INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5532,'Cell 2 Region 3',5530); INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5540,'Region 4',5500); INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5533,'Cell 1 Region 4',5540); INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5534,'Cell 2 Region 4',5533); INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5590,'Region 5',5500); INSERT INTO dept(DEPT_ID,DEPT_NAME,DEPT_PARENT) VALUES (5591,'Cell 1 Region 5',5590); end; /
1 rows affected
 hidden batch(es)


with cte(node_id, dept_id, dept_parent, lvl) as ( select dept_id node_id, dept_id, dept_parent, 0 lvl from dept union all select c.node_id, d.dept_id, d.dept_parent, c.lvl + 1 from cte c inner join dept d on d.dept_id = c.dept_parent ) select dept_id ancestor, node_id node, lvl from cte where lvl > 0 order by node, ancestor
ANCESTOR NODE LVL
5500 5510 1
5500 5511 2
5510 5511 1
5500 5512 2
5510 5512 1
5500 5513 2
5510 5513 1
5500 5514 2
5510 5514 1
5500 5515 2
5510 5515 1
5500 5520 1
5500 5521 2
5520 5521 1
5500 5522 2
5520 5522 1
5500 5530 1
5500 5531 2
5530 5531 1
5500 5532 2
5530 5532 1
5500 5533 2
5540 5533 1
5500 5534 3
5533 5534 1
5540 5534 2
5500 5540 1
5500 5590 1
5500 5591 2
5590 5591 1
 hidden batch(es)