2

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!

jp3949
  • 21
  • 2
  • "SQL-agnostic"? Recursive CTEs are part of the SQL standard. Take it up with vendors if they don't (yet) support them. Many, if not most, databases do. – Gordon Linoff Feb 27 '20 at 19:47
  • Here it is : https://stackoverflow.com/questions/27631884/recursive-cte-find-all-employees-below-manager – Matias Barrios Feb 27 '20 at 20:21
  • Hi @MatiasBarrios, this is for sqlserver what you have sent ? OP needs it for MySQL. Also, he has the solution with cte ? – VBoka Feb 27 '20 at 20:24
  • @GordonLinoff This is the impression I got, was just looking for something that will work on older MySQL versions (which do not support CTEs). – jp3949 Feb 27 '20 at 21:17

1 Answers1

0

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

Souvik Nandi
  • 354
  • 2
  • 6
  • 1
    Thanks for your answer, but unfortunately this solution does not allow you to specify a given manager to find the subordinates for. It just lists out the entire table and manager for each person. If you wanted to specify a manager, it would only go 1 level deep because it isn't recursive. – jp3949 Feb 27 '20 at 21:16