Interesting SQL query requirement for password:   :: lost password

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

  •   Subject
  • Author
  • Posted on
Hi All

Wondered if you could help me with the below query.

I have 1 simple table called STOCKCATS that consists of 2 fields.

These fields are called CATID and LEVEL.

The contents of this table are as follows:

cat01       <nothing>
cat02       <nothing>
cat03       cat01
cat04       <nothing>
cat05       cat01
cat06       cat02
cat07       cat04
etc..        etc...

The way this table works is that I have an ASP page that allows the user to
create a stock category at 2 levels, category level and sub-category level.

When I file the entered data into the table, if the user has chosen to
create a category level stock category then the LEVEL field is left blank
and if they chose to create a sub-category level category then I post the
relevant category level stock category code in the LEVEL field.  For
example, in the above list cat01 is a category level stock category and
cat05 is a sub-category as it is a sub-category of cat01.

My query is that I want to populate a simple HTML <SELECT> menu (using ASP),
but instead of it being a straightforward 'select catid from stockcats order
by catid', I want to group this list into some kind of order, eg:

instead of:

cat01       <nothing>  << I need to bring back this 2nd column so that I can
do a simple IF THEN in asp to indent sub-cats
cat02       <nothing>
cat03       cat01
cat04       <nothing>
cat05       cat01
cat06       cat02
cat07       cat04

I would like

cat01       <nothing>  << ditto
cat03       cat01
cat05       cat01
cat02       <nothing>
cat06       cat02
cat04       <nothing>
cat07       cat04

Do you know if this is possible in pure SQL (I must confess that I'm using
MySQL, but I would have thought the SQL syntax would be the same if it is
possible) or a combo of ASP & SQL?



Re: Interesting SQL query requirement for menu


Many thanks.

Rgds Robbie

Astra wrote:
Quoted text here. Click to load it

Here's how I'd do it:

   SELECT s.catid, s.level
   FROM stockcats AS s
   ORDER BY COALESCE(s.level, s.catid), s.catid

I'm assuming that by <nothing> you mean that the field has a NULL state.
COALESCE is a standard SQL function that returns its first non-null
argument.  I list a secondary sort field so that cat03 and cat05 will be
sorted correctly, since both have the same level field.

By the way, if you ever go to three levels, or unlimited levels, of
subcategorization, you'll need to store trees.  It gets a lot harder to
manipulate heirarchies in SQL by storing simply the parent-child
relationship as you are doing.  I recommend Joe Celko's book "SQL for
Smarties"; it has a chapter on manipulating heirarchies and trees in SQL.

Bill K.

Site Timeline