0

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?

ursitesion
  • 2,071
  • 7
  • 32
  • 45
  • 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 http://www.slideshare.net/billkarwin/models-for-hierarchical-data from @BillKarwin , a regular of this forum. – jynus Sep 11 '14 at 12:45

2 Answers2

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

Give it a Try !!!

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
0

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

Community
  • 1
DerekCate
  • 438
  • 1
  • 4
  • 7