How to find a manager's level in mysql?

Question

So I have a giant table of 100K employees. Every employee has a supervisor except our top boss.

I want to capture a manager's level from the bottom. Sounds simple but now I have a series of views created at every single level - 9 times.

Example

SELECT DISTINCT
reports__allmanagers.supervisorid
FROM
reports__allmanagers 


SELECT
ldap_large.uid,
ldap_large.mail,
ldap_large.jobcode,
ldap_large.thomsjobcodedescr,
ldap_large.supemail,
ldap_large.thomssupervisorid, 
CASE
        WHEN uid LIKE '9999' THEN '9999'
                ELSE '2'
      END as man_level
FROM
reports_man2ids
INNER JOIN ldap_large ON reports__man2ids.supervisorid = ldap_large.uid 

This is just the two views for level 2. At the end I combine the views via a query and form a static daily table. It works but is highly highly inefficient. Is there an easier way to do this?

(The other issue I have when doing this is that our company isn't a perfect tree. So in my views a few supervisors sneak in to 2-3 different levels. In the end I need there to be only one row for each supervisor. So obviously uppermanagement will be level one one first view so we just discard the lower man_levels.)

Answer 1

You will want to create a recursive query that will run until I gets to the top boss. Keep some sort of counter to know what level you are currently at. From the information you've given I'm not quite sure what the schema is exactly but this question has a lot of great answers for writing a recursive query: How to create a MySQL hierarchical recursive query

Taken from one of the answers:

Table structure

+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+

Query:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

Output

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+

Most users at one time or another have dealt with hierarchical data in a SQL database and no doubt learned that the management of hierarchical data is not what a relational database is intended for. The tables of a relational database are not hierarchical (like XML), but are simply a flat list. Hierarchical data has a parent-child relationship that is not naturally represented in a relational database table. Read more

Refer the blog for more details.

EDIT:

select @pv:=category_id as category_id, name, parent from category
join
(select @pv:=19)tmp
where parent=@pv

Output:

category_id name    parent
19  category1   0
20  category2   19
21  category3   20
22  category4   21