0

I have been looking at the query resulting total number of employees under an superior emp_id that has been passed to the query.

Example : for emp_id 1 he has '2,3,4,5' as reportees and under again for emp_id 3 there are '6,7' are reportess and again under emp_id 6 there are '8,9' are reportees and again under emp_id 9 there is a reportee '10'

Now when I pass the manger_id '1' to the Query I should get all 9 employees.

Below is my query, that is not working if there are 40 employees under an single employee. 

    SELECT manager_id,emp_id FROM users ,(SELECT @head := 2) head
    WHERE  FIND_IN_SET(manager_id, @head) > 0 AND @head := CONCAT(@head, "," , emp_id) ORDER BY manager_id

In my table structure there is a manager_id column for each emp_id

Please help either with the MySQL query of MySQL function.

user93068
  • 31
  • 4

2 Answers2

0

Do you mean recursive query?

If yes, You can read this document: Trees in SQL: Nested Sets and Materialized Path

This way not use query to get tree data, are use table structure to get same data. It is easy to query.

Karl Lin
  • 307
  • 4
  • 16
0

Please find below query. You can either put manager id value hard coded in "initialization" part or you can pass variable.

select  EmpID,
        ManagerID 
from    (select ManagerID, EmpID from employee
         order by ManagerID, EmpID) employee_sorted,
        (select @pv := '1') initialization
where   find_in_set(ManagerID, @pv) > 0
and     @pv := concat(@pv, ',', EmpID)
Sandesh
  • 1,036
  • 5
  • 13
  • This is the query which I have posted in the in Question, But this is not working for some scenarios where reportees exceeds more that 35. – user93068 May 25 '16 at 09:50