Genius!!

Many thanks.

Rgds Robbie

"Bill Karwin" <bill@karwin.com> wrote in message
news:dko5n102m59@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.