Get a path from manager to an employee in mysql 8-CodePudding

I have a question in term of my database courses, hope that you can help me. The table bellow is an exemple:

 ------------------------------------ 
|ID_EMPLOYEE|ID_MANAGER|NAME_EMPLOYEE|
 1          |2         |A             
|2          |3         |B            |
|3          |4         |C            |
|4          |5         |D            |
|5          |10        |E            |
|6          |8         |F            |
|7          |9         |G            |
|8          |10        |H            |
|9          |10        |I            |
|10         |11        |J            |
 ------------------------------------

The question is when user choose an person as manager then an other employee as her/his staff, how to print the path from manger to the staff. For example: when I choose 10 as manager then 2 as the staff, the result must be like this: 10->5->4->3->2 when I choose 5 as manager then 4 as the staff, the result must be: 5->4

I tried to use recursive, it can be done with the paths for all employee to manager but when I can't solve this requirement.

Thank you in advance.

CodePudding user response：

I wrote this recursive CTE to solve this problem, but then realized I have the results backwards from what you desire. You want Manager to Employee, but I wrote as Employee to Manager. I need to run out for an appointment and may not have time to correct this today, sorry. But hopefully this gets you in the right direction.

    WITH RECURSIVE routes AS (
      SELECT    id_manager,
                id_employee || '->' || id_manager as route
      FROM employees
      WHERE id_employee = 2
      UNION ALL
      SELECT    e.id_manager, 
                r.route || '->' || e.id_manager as route
      FROM routes r
      inner join employees e
        on e.id_employee = r.id_manager
    )
    SELECT r.route
    FROM routes r
    where r.id_manager = 10

Output:

    2->3->4->5->10

EDIT: Second mistake, I didn't write to MySql but rather Postgres. My bad, I'll delete this shortly.

CodePudding user response：

I want to say thank to @Isolated, here is the sql query for my question:

WITH RECURSIVE
cte ( node, path, lvl )
AS
( SELECT node, cast(node AS char(10000)), 0 as lvl   
          FROM (select id_employee as node, id_manager as parent from employees) as bst WHERE node = '10'
  union all
  SELECT bst.node,  CONCAT ( cte.path, '-->', bst.node ), 1   lvl
         FROM cte JOIN (select id_employee as node, id_manager as parent from employees) as bst ON cte.node = bst.parent
)
SELECT * FROM cte where cte.node = '2'
ORDER BY lvl asc