Help with query to loop over table...

[robstevens]

Hi,

I'm having trouble putting together a query in ms sql, could someone point me in the right direction.

Want I have is one table in this format;

menu | submenu
----------------------
menu1 | submenu1
menu2 | submenu2
menu3 | submenu3
menu3 | submenu5
submenu1 | submenu4
submenu2 | submenu4
submenu5 | submenu6
submenu3 | submenu4
submenu4 | BLANK
submenu6 | BLANK

It holds a menu system and shows, for example menu1 has options of submenu1, submenu1 has options of submenu4 and submenu4 has no further menus.

What I need is a query to list the submenu's a particular menu has underneath it.

e.g.

If I gave the query the following input-- ('menu1', 'menu2', 'menu3')
It would return this;

menu1 submenu1
menu1 submenu4
menu2 submenu2
menu2 submenu4
menu3 submenu3
menu3 submenu4
menu3 submenu5
menu3 submenu6

As there can be any nnumber of submenus to a menu I cannot work out how to get the sql to looping over the table. Any help/ideas greatly appreciated.

Cheers.

 

[PHV]

Have you tried a recursive stored procedure ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

does "any number of submenus" refer to the number of children one level down, or the number of levels?

in practice, no user-friendly menu system has an unlimited number of levels that are visible all at once

for example, visit any web search directory (e.g. yahoo or dmoz) and you will see actual links shown only one or two levels down

however, it is reasonable to do a count of all descendents (children, grandchildren, etc.)

there are only two ways to do this -- a recursive call, as PHV suggested, which, as should be obvious, throws the onus on your application logic to perform the counting -- or a series of LEFT OUTER JOINs, such that the number of joins is equal to the maximum number of levels in the hierarchy

well, actually, there's a third way, using joe celko's nested set model, but this would require a complete overhaul of your data structure and all the code that supports it



r937.com | rudy.ca