CREATE TABLE EMPL ( NAME VARCHAR(15), EMPID INT NOT NULL PRIMARY KEY, MGRID INT );
INSERT INTO EMPL VALUES ('Sal', 1, NULL), ('Berni', 2, 1), ('Serge', 3, 2), ('Kathy', 4, 2), ('David', 5, 1), ('Katherine', 6, 5), ('Miro', 7, 6), ('John', 8, 6), ('Rick', 9, 6), ('Eric', 10, 7), ('Steve', 11, 7), ('Haiwei', 12, 7), ('Yuri', 13, 8), ('Kaarel', 14, 8);
WITH n(empid, name, mgrid) AS (SELECT empid, name, mgrid FROM EMPL WHERE name = 'Sal' UNION ALL SELECT nplus1.empid, nplus1.name, nplus1.mgrid FROM EMPL as nplus1, n WHERE n.empid = nplus1.mgrid ) SELECT empid, name, mgrid FROM n;
-- With level WITH n(empid, name, mgrid, lvl) AS (SELECT empid, name, mgrid, 1 as lvl FROM EMPL WHERE name = 'Sal' UNION ALL SELECT nplus1.empid, nplus1.name, nplus1.mgrid, n.lvl+1 as lvl FROM EMPL as nplus1, n WHERE n.empid = nplus1.mgrid ) SELECT empid, name, mgrid, lvl FROM n; Results: EMPID|NAME|MGRID|LVL 1|Sal|(null)|1 2|Berni|1|2 5|David|1|2 3|Serge|2|3 4|Kathy|2|3 6|Katherine|5|3 7|Miro|6|4 8|John|6|4 9|Rick|6|4 10|Eric|7|5 11|Steve|7|5 12|Haiwei|7|5 13|Yuri|8|5 14|Kaarel|8|5