Find all people who work under a certain manager (SQL)

user_12975326 0
Viewed 95

I'm currently working on trying to find a list of every employee who works under a given manager. Right now, I have the following solution, which uses a recursive CTE: https://www.db-fiddle.com/f/oTTpJ9tTudkFrqy1WJghZj/0

The problem with this solution is that it only works with MySQL 8.0+, and I'm not sure how many of the other SQL distros support CTE. Is there a SQL-agnostic solution to this problem that will work with older versions of MySQL? I tried to solve this using loops where I had a function with a while loop that selected employee ids where manager id in (list of employee ids from previous iteration) and concatenated the result set, but you can't actually use GROUP_CONCAT in a where in clause as far as I can tell.

Any ideas would be greatly appreciated, thanks!

recursion common-table-expression mysql sql loops
1 Answers

I think this can be solved using traditional way of SQL

SELECT 
e.employee_id AS EmployeeId,
CONCAT(e.first_name, ' ', e.last_name) AS EmployeeName,
CONCAT(m.first_name, ' ', m.last_name) AS ManagerName
FROM Employees e
INNER JOIN Employees m ON e.manager_id = m.employee_id
ORDER BY m.employee_id, e.employee_id

Hope this will reduce much complexity

user_78830040
Related