Fog Creek Software
Discussion Board

sql question

if I have a table like example below..

menu    sub_menu    sub_sub_menu
------    ------------    ------------------
info      price            item
info      price            penny
info      price            coin
info      quality        product
info      quality        amount
cont      name          tel
cont      name          dept

I want to build a html menu of the above -like:


Is there an sql command I can hit the above table with that will give me the results to build the menu from one query.
Its only ordinary sql, not plsql, I would probably use it on Access or Postgres.

My solution would require multiple sql queries.. e.g. (UNIQUE FROM menu.. then.. FROM sub_menu WHERE menu=blah.... then.. FROM sub_sub_menu WHERE sub_menu=blah..)- have this running from a loop in ASP or perl to build the menu.. So requires multiple SQL queries..

and Im just wondering if there is a way to do it just from one query..

and if there is..
how would you separate the list it gave out so that you could build the menu above, knowing when to start a new menu or sub_menu.

aku beg
Wednesday, August 06, 2003

SELECT * FROM tMENU WHERE menu = 'info';


Eric Debois
Wednesday, August 06, 2003

that aint funny man!

aku beg
Wednesday, August 06, 2003

I'd say "SELECT * from tMenu", if you fancy augmented by some ORDER BY stuff. What exactly would you like the output from your one query to be?

Just me (Sir to you)
Wednesday, August 06, 2003

true.. true..

bollox.. looks like I will have to do multiple loop and multiple queries..

I guess a nice associative array might be what i need back from one query.

aku beg
Wednesday, August 06, 2003

One query with one loop would do it.

A select *, which is probably not too bad in this case since you need all the data anyway, and then a single loop (as long as you select it all in the correct order) would get it all for you.

Wednesday, August 06, 2003

Trees and SQL sucks basically. One way to do it is by using the CONNECT BY SQL clause available in Oracle and PostgreSQL. I've found a patch that claims to add hierarchical querys to Postgres, but I havent tried it.

I did this litte test, here is my table...

create table menupicks(
menupick          number,
caption              varchar2(10),
submenuof        number,
rootmenuname varchar2(20)

We need some data, and this is what I came up with:

1    File          null    FILEMENU
2    Open      1      FILEMENU
3    Save        1      FILEMENU
4    Save as  1      FILEMENU
5    Exit          1      FILEMENU
6    Help        null  HELPMENU
7    About      6      HELPMENU

There...the data relations here should be obvious, so then we have the query, which should look something along the lines of:

SELECT caption
  FROM menupicks
START WITH menupick = 1  -- Change here for each menu
CONNECT BY PRIOR menupick=submenuof;

So, you need to run the above query one time per "root" menu you have in your application.

Here is the PostgreSQL patch:


Wednesday, August 06, 2003

Wednesday, August 06, 2003

aku beg - here's some pseudo code:

results = execute("SELECT menu,sub_menu,sub_sub_menu FROM table ORDER BY menu,sub_menu,sub_sub_menu")

prevMenu = null
prevSubMenu = null
prevSubSubMenu = null

do until results.EOF
  if(results["menu"] != prevMenu)
      print "<div style='margin-left:1em'>"
        + results["menu"] + "</div>"
  if(results["sub_menu"] != prevSubMenu)
      print "<div style='margin-left:2em'>"
        + results["sub_menu"] + "</div>"
  if(results["sub_sub_menu"] != prevSubSubMenu)
      print "<div style='margin-left:3em'>"
        + results["sub_sub_menu"] + "</div>"
  prevMenu = results["menu"]
  prevSubMenu = results["sub_menu"]
  prevSubSubMenu = results["sub_sub_menu"]

Duncan Smart
Wednesday, August 06, 2003

ADO has a "SHAPE" command to produce a hierarchical recordset...

Wednesday, August 06, 2003

flippin hek.. thanks..
i will try these suggestions out..

aku beg
Wednesday, August 06, 2003

Anonymizer that is freaking brilliant!  I would have never thought of that.  I'm currently working on a data model to store messages that can have a hierarchy of elements.  The furthest I got was having a self-referential key.  I figured I'd just figure out how to write the sql later.

This is a much better approach!

Wednesday, August 06, 2003

Oracle has a whole lot of hierarchy extensions if you happen to be using it - CONNECT BY and friends.

Wednesday, August 06, 2003

*  Recent Topics

*  Fog Creek Home