Skip to content

Instantly share code, notes, and snippets.

@niisar
Created May 19, 2014 21:30
Show Gist options
  • Save niisar/001b41dad71456f9aa06 to your computer and use it in GitHub Desktop.
Save niisar/001b41dad71456f9aa06 to your computer and use it in GitHub Desktop.
spin thru company table starting at manager_id = 3. add the manager's name to the results.
WITH RSFC(EMPID,ENAME,MGRID,LVL) AS
(SELECT EMPLOYEE_ID,
EMPLOYEE_NAME,
MANAGER_ID,
0 AS LVL
FROM COMPANY
WHERE MANAGER_ID = 3
UNION ALL
SELECT EMPLOYEE_ID,
EMPLOYEE_NAME,
MANAGER_ID,
LVL+1
FROM RSFC R
INNER JOIN COMPANY F
ON F.MANAGER_ID = R.EMPID
)
SELECT a.EMPID,
a.ENAME,
a.MGRID,
a.LVL,
B.EMPLOYEE_NAME AS MGR_NAME
FROM RSFC a
LEFT JOIN COMPANY B
ON a.MGRID = B.EMPLOYEE_ID
ORDER BY a.LVL,
a.EMPID
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment