how to use recursive query in mysql

Question

Hello i have a question related to tree like structure that i try to shown you in table

id       name           reportsto
2        Akas           345
3        grity          345
4        kethy          2
5        alex           345
6        holy           3
7        dean           4
8        ambrose        3
9        amily          456

now reports to contains parent id like when i am login with reportsto id 345 then the contacts that reports to 345 shown and also contacts that reported to other id that id reported to 345 just like parent child relationship.so finally i want result like 2,3,4,5,6,7,8 but not id 9.so how to achieve that results with mysql query,i have a table that contains many data and its like tree structure.please solve. i used query but its not deliver exact result

select  contactid,firstname,lastname,reportsto from(select * from contactdetails order by reportsto, contactid) contact,(select @pv := '345') initialisation where   find_in_set(reportsto, @pv) > 0 and @pv := concat(@pv, ',', contactid)

Have you seen [this SO post](http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) ? — Tim Biegeleisen, Sep 02 '16 at 12:52
You should do it in MySQL. You have two options. First, you can do it manually by self joining your table some number of times. Second, you could also use dynamic SQL. — Tim Biegeleisen, Sep 02 '16 at 13:00
@TimBiegeleisen how to do it using dynamic sql queries,please elaborate — phpdev, Sep 02 '16 at 13:03

how to use recursive query in mysql

0 Answers0