MySQL 5.7 recursive query on hierarchy data with unknown levels

Question

I am developing a hierarchy data structure where I want to perform a recursion to get the desired output

 id       role_id      reporting_to
 ------+-------------+-----------------
1          100             101
1          101             102
1          102             103
1          103             104

I would like to get the following output if I gave 103 as input role

  id    role_id      reporting_to
-----------+-------------+-----------------
    1          100             101
    1          101             102
    1          102             103

The output should be from the input to till the last child. Tried following example, but it is not returning proper output

select id,role_id,reporting_to
from (select * from role_mapping order by reporting_to,id) rm_sorted,
     (select @r:='103') initialisation
where find_in_set(reporting_to, @r)
and length(@r := concat(@r, ',', role_id))

Answer 1

mysql5.7 can never do recursive queries unless you use functions.

You can also use other languages with recursion, such as mysql5.8, oracle, java, etc., to generate a path column, like this (assuming 104 is the root node)

id	role_id	reporting_to	path
1	100	101	104/103/102/101/100
1	101	102	104/103/102/101
1	102	103	104/103/102
1	103	104	104/103