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?

  • There are several models to store hierarchical data, depending on the rest of the operations that you intend to do on that dataset. I would recommend having a look at slideshare.net/billkarwin/models-for-hierarchical-data from @BillKarwin , a regular of this forum. – jynus Sep 11 '14 at 12:45

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

Give it a Try !!!

|improve this answer|||||

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

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.