How to get the top hierarchical data of user using mysql?

Question

Helllo,

My database table structure is as below.

TableName:- UserTable

id | empName | reportsTo
-----------------------
1  | XYZ     | -
2  | ABC     | 1
3  | MNP     | 2
4  | IJK     | 3
5  | PQR     | 4
6  | DEF     | 3
7  | STU     | 2

How can I get the details of user he reports to in top hierarchy.

Eg. When I select user whose id is 7 the output should be

id    empName  reportsTo
2     ABC      1
1     XYZ      -

same way when I select user whose id is 6 it should return data of id's 3,2,1.

why would you get the data from id 1 & 2 when you select user 7? what is it based on? and what is reporstTo? is this the id of the user he has to report whatever? — Christopher Supertramp, Jul 22 '18 at 15:12
or does the "2" @ reportsTo on user with id 7 say he has to report for the first 2 persons in the database? — Christopher Supertramp, Jul 22 '18 at 15:15
try this: `select * from UserTable where id<=(select reportsTo from UserTable where id=7)` — ferhado, Jul 22 '18 at 15:17
@Christopher Supertramp `ABC` is direct boss of `MNP` and `STU` — Talk2Nit, Jul 22 '18 at 15:19
@FerhadOthman im not sure, but will this not cause troubles if a boss has a higher id then the other person? — Christopher Supertramp, Jul 22 '18 at 15:20
As I see he want all rows having id less than or equals to reportsTo of the id wich he want — ferhado, Jul 22 '18 at 15:23
Possible duplicate of [Getting all parent rows in one SQL query](https://stackoverflow.com/questions/2441821/getting-all-parent-rows-in-one-sql-query) — P.Salmon, Jul 22 '18 at 15:24
but that sounds not very clever to me as for this solution the database must be sorted correct? what if there comes a new boss and his id is like 25? if you change the reportsTo then to 25 all people from 1-25 would be his bosses.. — Christopher Supertramp, Jul 22 '18 at 15:25
so you could either make a "order" field which then gets used like this example but you have to order the bosses correct (if thats even possible) or there must be another solution — Christopher Supertramp, Jul 22 '18 at 15:29
@P.Salmon Please don't mark this question as duplicate. You refered question is different. That gives output from top to bottom hierarchy, my expected output is bottom to top — Talk2Nit, Jul 22 '18 at 15:34

JORDAN MI · Answer 1 · 2018-07-22T17:02:16.323

There's a temporary solution but NOT GOOD to continue using it. You have to drop your table and create new organized one

First count different reportsto values

SELECT COUNT(DISTINCT reportsto) FROM test

You will get a number. let's suppose you get (5) then there're 5 are bosses of some employees

SELECT * FROM test WHERE 
id=(SELECT @boss := reportsto FROM test WHERE id=7) 
OR 
id=(SELECT @boss1 := reportsto FROM test WHERE id=@boss)
OR 
id=(SELECT @boss := reportsto FROM test WHERE id=@boss1)
OR 
id=(SELECT @boss1 := reportsto FROM test WHERE id=@boss)
OR 
id=(SELECT @boss := reportsto FROM test WHERE id=@boss1)

If you get (10) then the query'd be

SELECT * FROM test WHERE 
id=(SELECT @boss := reportsto FROM test WHERE id=7) 
OR 
id=(SELECT @boss1 := reportsto FROM test WHERE id=@boss)
OR 
id=(SELECT @boss := reportsto FROM test WHERE id=@boss1)
OR 
id=(SELECT @boss1 := reportsto FROM test WHERE id=@boss)
OR 
id=(SELECT @boss := reportsto FROM test WHERE id=@boss1)
OR 
id=(SELECT @boss1 := reportsto FROM test WHERE id=@boss)
OR 
id=(SELECT @boss := reportsto FROM test WHERE id=@boss1)
OR 
id=(SELECT @boss1 := reportsto FROM test WHERE id=@boss)
OR 
id=(SELECT @boss := reportsto FROM test WHERE id=@boss1)
OR 
id=(SELECT @boss1 := reportsto FROM test WHERE id=@boss)

.

and so on

again, im not sure but I think this will not work as a boss can also have a higher ID if he got safed in the database later? — Christopher Supertramp, Jul 22 '18 at 15:23
he said in a comment, that reportsTo means, that this is his boss.. and then his boss maybe also have a boss which is then also the boss of him.. — Christopher Supertramp, Jul 22 '18 at 15:27

score 0 · Answer 2 · answered Jul 22 '18 at 15:45

I don't think you understood the possible duplicate link I posted so here's the solution posted there with some trivial tweaks to suit your data.

drop table if exists usertable;
create table usertable(id int, empName varchar(3), reportsTo int);
insert into usertable values
(1  , 'XYZ'     , null),
(2  , 'ABC'     , 1),
(3  , 'MNP'     , 2),
(4  , 'IJK'     , 3),
(5  , 'PQR'     , 4),
(6  , 'DEF'     , 3),
(7  , 'STU'     , 2);

SELECT T2.id, T2.empname,t2.reportsto
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := reportsto FROM usertable WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 7, @l := 0) vars,
        usertable h
    WHERE @r <> 0) T1
JOIN usertable T2
ON T1._id = T2.id
where t2.id <> 7
ORDER BY T1.lvl;

+------+---------+-----------+
| id   | empname | reportsto |
+------+---------+-----------+
|    2 | ABC     |         1 |
|    1 | XYZ     |      NULL |
+------+---------+-----------+
2 rows in set (0.03 sec)

and for id 6

SELECT T2.id, T2.empname,t2.reportsto
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := reportsto FROM usertable WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 6, @l := 0) vars,
        usertable h
    WHERE @r <> 0) T1
JOIN usertable T2
ON T1._id = T2.id
where t2.id <> 6
ORDER BY T1.lvl; 

+------+---------+-----------+
| id   | empname | reportsto |
+------+---------+-----------+
|    3 | MNP     |         2 |
|    2 | ABC     |         1 |
|    1 | XYZ     |      NULL |
+------+---------+-----------+
3 rows in set (0.00 sec)

Note I have changed reportsto from - to null for id 1. With this only works if reports to is less than id if not you will have to have some serious thought about your data structure.

How to get the top hierarchical data of user using mysql?

2 Answers2