Recursion in MySQL

Question

Let's say that I have mysql table like this:

nav_id | nav_en_name | parent_id | level
      1   root                  0       0
      2   category              1       1
      3   subcategory           2       2
      4   2nd category          1       1
      5   2subcategory          2       2

And I want to get for every nav_id, path to this cat. For example

nav_id | path
      1 | root
      2 | root/category
    ...
      5 | root/category/2subcategory

How can I do it in MySQL? Max possible level is 3.

Thanks for the help ;)

From comments: I've tried something like:

 SELECT nav.nav_id, nav2.nav_id, nav3.nav_id, nav.nav_en_name, nav2.nav_en_name, nav3.nav_en_name FROM menu as nav LEFT JOIN (SELECT * FROM menu WHERE level = 2) as nav2 on nav.nav_id = nav2.parent_id LEFT JOIN (SELECT * FROM menu WHERE level = 3) as nav3 on nav2.nav_id = nav3.parent_id

//edit

Ok, guys. This function helped me: Mysql query which returns category tree

Thanks ;)

This may help. http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query — Juan Carlos Oropeza, Mar 02 '16 at 14:21
Why won't it help in your case? What about your case is unique? What have you tried? — bishop, Mar 02 '16 at 14:36
I think you have to read a litle more on that question and maybe you learn something. You have the query version and also a sample the join the table with itself 3 times — Juan Carlos Oropeza, Mar 02 '16 at 14:40
@bishop i've tried something like SELECT nav.nav_id, nav2.nav_id, nav3.nav_id, nav.nav_en_name, nav2.nav_en_name, nav3.nav_en_name FROM menu as nav LEFT JOIN (SELECT * FROM menu WHERE level = 2) as nav2 on nav.nav_id = nav2.parent_id LEFT JOIN (SELECT * FROM menu WHERE level = 3) as nav3 on nav2.nav_id = nav3.parent_id but how can I get nav_id of the highest level? — alanmcknee, Mar 02 '16 at 14:43
Check out this article, it might help you. http://www.sqlteam.com/article/more-trees-hierarchies-in-sql — minitauros, Mar 02 '16 at 15:03
No built in recursion in mysql. You'd have to use PostgreSQL. You can probably use that hierarchical mysql link Juan posted. — Mistergreen, Mar 02 '16 at 15:04
@Mistergreen Yes, this gives me paths. But how can I get the id of the "leaf"? I have to in some way get full paths, as I said before, for every `nav_id` in my table. — alanmcknee, Mar 02 '16 at 15:12
Try just getting the nav_id. Leave the name out of it. You should get paths of ID instead. If you need the name of the ID, do a separate query to get the names. — Mistergreen, Mar 02 '16 at 15:20

Recursion in MySQL

0 Answers0