365 Days of Daily Coding: Day 118

Yay to me for the consistency. If you are reading this, know that it’s difficult but it’s going to be absolutely rewarding one day and you are going to achieve all your ambitions.

Puzzle 2: Managers and Employees

I tried the solution in the live oracle environment which you can find here.

Solution:

WITH cte_Recursion AS
(
SELECT	EmployeeID, ManagerID, JobTitle, Salary, 0 as Depth
FROM	#Employees a
WHERE	ManagerID IS NULL
UNION ALL
SELECT	b.EmployeeID, b.ManagerID, b.JobTitle, b.Salary, a.Depth + 1 as Depth
FROM	cte_Recursion a INNER JOIN 
		#Employees b ON a.EmployeeID = b.ManagerID
)
SELECT	EmployeeID, ManagerID, JobTitle, Salary, Depth
FROM	cte_Recursion;

For this solution, I wasn’t able to replicate it in oracle as the union is not recursive unlike in TSQL. If you know the solution in PL/SQL, I would really appreciate if you could share.