MySQL - Keep finding superiors of employees until the most superior employee is reached

Question

I have an employees table in which all employees report to a superior except for one employee who reports to no one as he is the boss.

employeeNumber | reportsTo
---------------|-----------------
1002           | null
1056           | 1002
1143           | 1056
1165           | 1143
...

I want to be able to show the employee numbers of all employees who are superiors of a particular employee. (i.e. the person he reportsTo, and the employee who that person reportsTo, ... all the way up)

For e.g. employeeNumber 1165 should yield

   | employeeNumber |
   |----------------|
   | null           |
   | 1002           |
   | 1056           |
   | 1143           |

I have written a MySQL statement which carries out the query but I believe this is a bit too complex to read and understand:

select reportsTo as employeeNumber from employees where employeeNumber in
(select reportsTo from employees where employeeNumber in (select reportsTo from employees where
employeeNumber = 1165)) union select reportsTo from employees where employeeNumber in 
(select reportsTo from employees where employeeNumber = 1165) union select reportsTo from employees
where employeeNumber = 1165;

What simple MySQL statement can I use to get the same result?

you can use CTE recursive query to get all parents.. so many similar cases here so i don't have to put an answer — RoMEoMusTDiE, Jun 07 '17 at 22:07
@maSTAShuFu, Recursive CTE is not supported until MySQL version 8.0, which is still under development. — Bill Karwin, Jun 07 '17 at 22:08
i see @BillKarwin... well I reckon a loop and temporary tables should help you out — RoMEoMusTDiE, Jun 07 '17 at 22:09

Bill Karwin · Answer 1 · 2022-10-21T23:30:05.687

I just gave a presentation about recursive queries in MySQL at Percona Live April 2017. The short answer is that there's no simple solution in MySQL before version 8.0 (which is still under development as I write this, and not expected to be GA for another year or so).

You can do joins to get the ancestor nodes, but you can only do a fixed number of joins, you can't predict how deep the hierarchy is.

SELECT 
  e1.employeeNumber AS emp1,
  e2.employeeNumber AS emp2,
  e3.employeeNumber AS emp3,
  e4.employeeNumber AS emp4,
  e5.employeeNumber AS emp5,
  e6.employeeNumber AS emp6
FROM employees AS e1
LEFT JOIN employees AS e2 ON e1.reportsTo = e2.employeeNumber
LEFT JOIN employees AS e3 ON e2.reportsTo = e3.employeeNumber
LEFT JOIN employees AS e4 ON e3.reportsTo = e4.employeeNumber
LEFT JOIN employees AS e5 ON e4.reportsTo = e5.employeeNumber
LEFT JOIN employees AS e6 ON e5.reportsTo = e6.employeeNumber
WHERE e1.employeeNumber = 1165

There are several other workarounds to query hierarchical data in MySQL, but they each require you to store the hierarchy data differently. You might like my presentation Recursive Query Throwdown or my past Stack Overflow answer to What is the most efficient/elegant way to parse a flat table into a tree?

I also wrote about methods of recursive queries in my book, SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.

score 0 · Answer 2 · answered Jun 07 '17 at 22:22

It's been a while since I've written actual SQL code (thanks you Enitity Framework), so I'll try to write this a bit in pseudo code which will probably lean towards C#. Sorry for that. Maybe somebody can translate it for me?

int EmployeeNumber = 1165;
int bigChiefNumber = GetChiefOf(EmployeeNumber);

private int GetChiefOf(int EmployeeNumber)
{
    if(employeeNumber.reportsTo == null)
    {
        return employeeNumber;
    } else
    { 
        return GetChiefOf(employeeNumber.reportsTo);
    }
}

Or somewhat shorter:

int BigChiefNumber = GetChiefOf(1165);

private int GetChiefOf(int employeeNumber)
{
    return (employeeNumber.reportsTo == null) 
        ? employeeNumber 
        : GetChiefOf(employeeNumber.reportsTo);
}

I guess in SQL you'd need to create a nested stored procedure. I hope this gets you the right direction :).

Edit: So, I didn't notice the MySQL part. I'm used to TSQL. I didn't know MySQL doesn't support recursive queries (not even with Stored Procedures ?). This means you can safely ignore my answer :(.

You can make a recursive stored procedure, and each invocation of the proc will run its own query, but that's different from a single recursive query returning a single result set of the full set of ancestors. But FYI you shouldn't use MySQL stored procs. They suck. — Bill Karwin, Jun 08 '17 at 01:33

score 0 · Answer 3 · answered Jun 07 '17 at 22:25

0

Might want to consider using a user defined function with a while loop. The loop would recurse until it reaches a stop value (perhaps 0?) and the udf would return a single scalar that could be incorporated into the select clause.

answered Jun 07 '17 at 22:25

dbl4k

71
6

RoMEoMusTDiE · Answer 4 · 2017-06-07T23:04:21.273

This procedure should help you out

Loop until you get the NULL superior and temporary table

CREATE PROCEDURE GetList(me int)
BEGIN



create table mytable (id int, reportsto int);

create table mytemp  (id int);

insert into mytable 
values 
(1002, NULL ),
(1056, 1002 ),
(1143, 1056 ),
(1165, 1143 );


set @parent:=me;

insert into mytemp
values (@parent);

WHILE @parent IS NOT NULL DO

  insert into mytemp
  select reportsto from mytable where id=@parent;

  select @parent:=reportsto  from mytable where id=@parent;

end while;

select * from mytemp;

drop table mytable;    
drop table mytemp;

END;

MySQL - Keep finding superiors of employees until the most superior employee is reached

4 Answers4