Fetching hierarchical data in mysql

Question

I have a user table ,

USER_ID  IMMEDIATE_SUPERIOR_ID
432      NULL
554      432
1150     432
1442     1150

To fetch hierarchical data I wrote this query

SELECT c1.user_id as level_1, c2.user_id as level_2, 
c3.user_id as level_3, c4.user_id as level_4 ,
c4.user_id as level_5 , c4.user_id as level_6 
FROM `user` as c1
    LEFT JOIN `user` as c2 ON c1.user_id =c2.IMMEDIATE_SUPERIOR_ID 
    LEFT JOIN `user` as c3 ON c2.user_id =c3.IMMEDIATE_SUPERIOR_ID 
    LEFT JOIN `user` as c4 ON c3.user_id =c4.IMMEDIATE_SUPERIOR_ID
    LEFT JOIN `user` as c5 ON c4.user_id =c5.IMMEDIATE_SUPERIOR_ID 
    LEFT JOIN `user` as c6 ON c5.user_id =c6.IMMEDIATE_SUPERIOR_ID 
    where  c1.USER_ID =432

Result :

level_1|level_2|level_3|level_4|level_5|level_6|
-------+-------+-------+-------+-------+-------+
    432|    554|       |       |       |       |
    432|   1150|   1442|       |       |       |

How can I show the output like below since user 432 is the supreme manager of all these three users?

    USER_ID  IMMEDIATE_SUPERIOR_ID 
    554      432
    1150     432
    1442     432

@AliFidanli Query is more good as We can use it code to fetch the data — Yogus, Nov 18 '21 at 09:43

Akina · Accepted Answer · 2021-11-18T09:53:13.673

the version i checked is 8.0.22 – Yogus

WITH RECURSIVE
cte AS ( SELECT *
         FROM user
         WHERE IMMEDIATE_SUPERIOR_ID = 432
       UNION ALL
         SELECT user.*
         FROM user 
         JOIN cte ON cte.user_id = user.IMMEDIATE_SUPERIOR_ID )
SELECT cte.user_id, 432 IMMEDIATE_SUPERIOR_ID 
FROM cte;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3acc566a4a7f356c0c48764be600c69a

If you need to gather the data for more than one root user then

WITH RECURSIVE
cte AS ( SELECT *, user_id UPPER_SUPERIOR_ID
         FROM user
         WHERE IMMEDIATE_SUPERIOR_ID IS NULL
         --  AND user_id IN ( {needed root users list} )
       UNION ALL
         SELECT user.user_id, user.IMMEDIATE_SUPERIOR_ID, cte.UPPER_SUPERIOR_ID
         FROM user 
         JOIN cte ON cte.user_id = user.IMMEDIATE_SUPERIOR_ID )
SELECT cte.user_id, UPPER_SUPERIOR_ID 
FROM cte
-- WHERE IMMEDIATE_SUPERIOR_ID IS NOT NULL
-- ORDER BY UPPER_SUPERIOR_ID, user_id
;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=de8997ad5d484eec9d6c7a8c1844f1c5

There is a great canonical answer for hierarchical queries like this one, as you are well aware. — Shadow, Nov 18 '21 at 09:49

Fetching hierarchical data in mysql

1 Answers1