Interesting SQL query requirement for <SELECT> menu

am 07.11.2005 15:44:33 von Oliver Weichhold

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
cat02
cat03 cat01
cat04
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 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 << I need to bring back this 2nd column so that I
> can
> do a simple IF THEN in asp to indent sub-cats
> cat02
> cat03 cat01
> cat04
> cat05 cat01
> cat06 cat02
> cat07 cat04
>
> I would like
>
> cat01 << ditto
> cat03 cat01
> cat05 cat01
> cat02
> cat06 cat02
> cat04
> 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
>

SELECT CATID, LEVEL
FROM STOCKCATS
ORDER BY COALESCE(LEVEL, CATID), CATID

--
May you be touched by His noodly appendage. RAmen.
http://venganza.org

Re: Interesting SQL query requirement for <SELECT> menu

am 08.11.2005 11:46:58 von Oliver Weichhold

Genius!!

Many thanks.

Rgds Robbie

"Chris Hohmann" wrote in message
news:eGoYuJ84FHA.3348@TK2MSFTNGP10.phx.gbl...
"Astra" wrote in message
news:Otneao64FHA.3976@TK2MSFTNGP15.phx.gbl...
> 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
> cat02
> cat03 cat01
> cat04
> 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