MySQL: How to fetch nth level child menus of a parent menu

Question

I have a menu which is parent of multiple menu items and each child menu has their own child menus. This pattern is continued till 5-6 vertical hierarchy level. I don't want to write a query which contains nth level subquery as below:

select id from jos_menu where parent in(select id from jos_menu where parent in(select id from jos_menu where parent=385 and published=1))

Also, I don't want to write a script for this. There should be something in MySQL to fetch such result.

Can someone guide me about how can I achieve my result in the best way?

Answer 1

A long, long time ago (Oct 24, 2011), in a galaxy far away, someone boldly asked

Find highest level of a hierarchical field: with vs without CTEs

In my answer to that post, I wrote three stored procedures to find specific relationships

• GetParentIDByID
• GetAncestry
• GetFamilyTree

If you use the code in GetFamilyTree, just pick the nth member of the output.

I have referred others to that post

• Jan 31, 2014 : Recursive Query in MySQL using stored proceedure and CURSOR
• Jul 11, 2013 : Recursive self joins
• Dec 10, 2012 : MySQL: Tree-Hierarchical query

Give it a Try !!!

Answer 2

When I was looking to see if MySQL supports CTE's (Common Table Expressions) I came across this: https://stackoverflow.com/questions/5291054/generating-depth-based-tree-from-hierarchical-data-in-mysql-no-ctes/5291159#5291159