I want to get menu_id related all menu_id i.e, for menu_id 3 it should return
3,4,6,11
Following is my table:
Asked
Active
Viewed 391 times
-2
-
Hi justin, Welcome to SO. This looks like a `Hierarchical/Dynamic select` query. You can not done with a simple select . – Subin Chalil Nov 25 '15 at 10:39
-
Hi Justin...Could you elaborate a bit more about your problem? on what basis you want the menu_ids from your table? – honey Nov 25 '15 at 10:40
-
making a menu list. for menu_id 3 it is the main menu and related to all menu_id 3 its submenu. if i select 3 it should return 3,4,6,11.. my menu should contain like 3 define user-->4 user groups-->6 admin details-->11 mail system – justin Nov 25 '15 at 10:52
-
That didnt help at all! If i got it right you get the menu parent from the row with id=3. This gives you 4. then you get the row with id=4, which gives you 6 and so on. right? – Skaros Ilias Nov 25 '15 at 10:55
-
yes.want to execute in a single query..thanks in advance – justin Nov 25 '15 at 10:58
-
as @SubinCPoonamgode mention, i also believe that this can not be done on a single select. maybe a sql function, or for sure using a programming language. I dont know any other way – Skaros Ilias Nov 25 '15 at 11:07
-
Possible you look fof something like this hierarchical queries in mysql http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query Unfortunatly, mysql doesn't support them yet but there are plently information on how to deal with it. – max Nov 25 '15 at 11:44
-
@justin if the level is fixed(a parent has max 7 hierarchical child()), then it can be done in single query.. – Subin Chalil Nov 25 '15 at 11:54
1 Answers
1
I think this query work for you:
SELECT @id := (SELECT ms_menu_id FROM ms_menu WHERE ms_menu_parent = @id)
AS menu_id FROM (SELECT @id := 3) vars STRAIGHT_JOIN ms_menu WHERE
@id !=0 union all (select ms_menu_id from ms_menu where ms_menu_id=3)
order by menu_id ASC limit 1, 18446744073709551615
results should be: 3,4,6,11
-
Interesting solution. Although only works if each parent only has one child. – Kickstart Nov 25 '15 at 13:32