Sql hierarchy tree

UDA

Sql hierarchy tree

I have 2 tables, tab1 and tab2. The tab1 table is a master table and stores all the emp_name. The tab2 table stores the relationship like emp_id and manager_id and both these columns are represented by the name_id in tab1. I need a query that will build all emp - manager hierarchy. Like if A, B , C report to D and D, E report to F, then the query should give A, B, C, D, E report to F.

I can use a recursive query with a temp table to achieve this but it will need a manager id at the root, it cannot be made generic to run for the entire query.

Anyone know how to recursively move through the tree with sql?

Thanks
Rahul
1 REPLY
N/A

Re: Sql hierarchy tree

hi maverick,

I have tried creating a tree struture of employee - manager relationship and tried wrote a Recursive SQL to get the requrird outpur let me know if it helps.
create volatile table t1 /* contains emp_id and his name */
(emp_id INTEGER, name VARCHAR(30)) on commit preserve rows;

create volatile table t2 /* Contains emplyee reltaiosnhop with his manger */
(emp_id INTEGER, mgr_id INTEGER) on commit preserve rows;

below values were inseted

INSERT INTO T1 VALUES(1, 'Wendy');
INSERT INTO T1 VALUES( 2, 'Muk') ;
INSERT INTO T1 VALUES( 3, 'Jim');
INSERT INTO T1 VALUES( 4, 'Kim');
INSERT INTO T1 VALUES( 5, 'Harsh');
INSERT INTO T1 VALUES( 6, 'Peter');
INSERT INTO T1 VALUES( 7, 'O''Neil');
INSERT INTO T1 VALUES( 8, 'Smitha');
INSERT INTO T1 VALUES( 9, 'Darren');
INSERT INTO T1 VALUES(10, 'Mike');
INSERT INTO T1 VALUES(11, 'Coldcase');
INSERT INTO T1 VALUES(12, 'Henry');
INSERT INTO T1 VALUES(13, 'Dario');
INSERT INTO T1 VALUES(14, 'Morris');
INSERT INTO T1 VALUES(15, 'Mills');
INSERT INTO T1 VALUES(16, 'Arun');
INSERT INTO T1 VALUES(17, 'Wein');

INSERT INTO T2 VALUES(1,10) ;
INSERT INTO T2 VALUES( 2, 10) ;
INSERT INTO T2 VALUES( 3, 10) ;
INSERT INTO T2 VALUES( 4, 10);
INSERT INTO T2 VALUES( 5, 11);
INSERT INTO T2 VALUES( 6, 11);
INSERT INTO T2 VALUES( 7, 12);
INSERT INTO T2 VALUES( 8, 12);
INSERT INTO T2 VALUES( 9, 12);
INSERT INTO T2 VALUES(10, 15);
INSERT INTO T2 VALUES(11, 16);
INSERT INTO T2 VALUES(12, 16);
INSERT INTO T2 VALUES(13, 15);
INSERT INTO T2 VALUES(14, 16);
INSERT INTO T2 VALUES(15, 17);
INSERT INTO T2 VALUES(16, 17);
INSERT INTO T2 VALUES(17, NULL);

Arun -Coldcase -- Harsh
| -- peter
-Henry -- O''Neil
| -- Smitha
| -- Darren
-Morris

WITH RECURSIVE RECEMP(EMP_ID,NAME,LEVEL1) AS
(
SELECT EMP_ID,NAME,0
FROM T1 WHERE NAME ='Arun'
UNION ALL
SELECT T2.EMP_ID,T1.NAME ,LEVEL1+1
FROM T1 ,
T2 ,
RECEMP
WHERE T1.EMP_ID = T2.EMP_ID AND
RECEMP.EMP_ID = T2.MGR_ID
and level1 < 10
)
SEL NAME,LEVEL1 FROM RECEMP ORDER BY LEVEL1;

running the above query would give you the 9 records( 8 relationship and 1 seed record i.e. Arun). you can restrict the number of records by adding filters to the bottom most query like SEL NAME,LEVEL1 FROM RECEMP ORDER BY LEVEL1 where LEVEL1 >0;

hope it helps :-)