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

[Hugo Kornelis]

Re: Interesting SQL query requirement for &lt;SELECT&g t; menu

On Mon, 7 Nov 2005 14:45:41 -0000, Astra wrote:
[color=blue]
>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.[/color]

Hi Robbie,

I'm not too happy with this design. Categories are not the same thing as
sub-categories, so you shouldn't lump them together in the same table.

CREATE TABLE Categories
(CatName varchar(10) NOT NULL,
PRIMARY KEY (CatName)
)
CREATE TABLE SubCategories
(SubCatName varchar(10) NOT NULL,
CatName varchar(10) NOT NULL,
PRIMARY KEY (SubCatName),
FOREIGN KEY (CatName) REFERENCES Categories (CatName)
)

[color=blue]
>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?[/color]

If you change the design as I suggest, then it's as simple as

SELECT CatName, NULL AS SubCatName
FROM Categories
UNION ALL
SELECT CatName, SubCatName
FROM SubCategories
ORDER BY CatName, SubCatName

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Re: Interesting SQL query requirement for &lt;SELECT&g t; menu

Astra (No@Spam.com) writes:[color=blue]
> 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?[/color]

I believe this query would work in SQL Server:

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

But I don't think it this conforms to ANSI standards, so it may not run
in MySQL.

If you want help with MySQL, you are probably better off asking in
comp.databases. mysql or some other MySQL forum.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Astra]

Re: Interesting SQL query requirement for &lt;SELECT&g t; menu

Many thanks guys

Apologies for multi-post.

Rgds Robbie


"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns9707F15 6E1626Yazorman@ 127.0.0.1...
Astra (No@Spam.com) writes:[color=blue]
> 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?[/color]

I believe this query would work in SQL Server:

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

But I don't think it this conforms to ANSI standards, so it may not run
in MySQL.

If you want help with MySQL, you are probably better off asking in
comp.databases. mysql or some other MySQL forum.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp