Interesting SQL query requirement for <SELECT> menu

[Astra]

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:

CATID LEVEL
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?

Thanks

Robbie

[Bill Karwin]

Astra wrote:

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:

I would like

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

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.

Regards,
Bill K.

[Astra]

Genius!!

Many thanks.

Rgds Robbie

"Bill Karwin" <bi**@karwin.com> wrote in message
news:dk*********@enews4.newsguy.com...
Astra wrote:

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:

I would like

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

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.

Regards,
Bill K.