0

I have a table that has employee_no and manager columns. If I want to get all employee's that report to a certain manager I can run a simple query.

SELECT * FROM employee_table WHERE manager = :employee_to_search

However, this only provides a result of people that directly report to this employee. I would like to return a result of ALL employee's that report, i.e.: further levels down the org structure.

How would I go about this? I don't have access to recursive query (MySQL 8)

SELECT  p.employee_no, prefer, lname
    FROM    (SELECT employee_no, manager FROM employee_table
                where live = 1
             ORDER BY manager, employee_no) p,
            (SELECT @pv := 111111) initialisation
    WHERE   FIND_IN_SET(manager, @pv) > 0
    AND     @pv := CONCAT(@pv, ',', employee_no)

(where employee to search is 111111)

However this doesn't return all records

j. marc
  • 333
  • 4
  • 11
  • 2
    I added the [tag:hierarchical-data] tag. There are a ton of answers about this on Stack Overflow already. I wrote one with a high score here: https://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/192462#192462 – Bill Karwin Sep 03 '22 at 01:32

0 Answers0