Interesting SQL query requirement for <SELECT> menu

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
Robbiecreate table #tbl (c1 int, c2 int)
insert into #tbl
select 1 ,null
union select 3,1
union select 5,1
union select 2,null
union select 6,2
union select 4,null
union select 7,4
select * from #tbl
order by isnull(c2,c1),c1
"Astra" <No@.Spam.com> wrote in message
news:ONC3ao64FHA.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 <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
>
>|||Hi Moshe
Many thanks for your prompt reply, but how this would work?
Where does this relate to my STOCKCATS table'
Rdgs Robbie
<Moshe> wrote in message news:%23xbjsy64FHA.4076@.tk2msftngp13.phx.gbl...
create table #tbl (c1 int, c2 int)
insert into #tbl
select 1 ,null
union select 3,1
union select 5,1
union select 2,null
union select 6,2
union select 4,null
union select 7,4
select * from #tbl
order by isnull(c2,c1),c1
"Astra" <No@.Spam.com> wrote in message
news:ONC3ao64FHA.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 <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
>
>|||instead
select * from #tbl
order by isnull(c2,c1),c1
I would use
select * from #tbl
order by isnull(c2,c1), c2
to be sure main level is always the first and sub items are below.
Little problem, if you need sort items alphabetically. Like:
Alpha
C...
R...
Beta
F...
R...
G...
select A.CATID, A.LEVEL, A.TEXT, (select B.TEXT from STOCKCATS B where
B.CATID=A.LEVEL) ParentText
from STOCKCATS A
order by ISNULL(ParentText, Text), c2, Text
but sorting by using ISNULL( varchar, ...) will be very slow. I think the
best way is separate it into 2 SELECTS. One for root items, and other one fo
r
getting subitems for each root item.|||On Mon, 7 Nov 2005 14:45:04 -0000, Astra wrote:

>Hi All
>Wondered if you could help me with the below query.
Hi Robbie,
I answered the exact same question in comp.databases.ms-sqlserver about
an hour ago.
Please do not post the same message independantly to several groups.
Pick one group; post there. Or, if you really think that the message is
appropriate for two groups, post ONE SINGLE message and crosspost it to
both groups. That way, all answers will appear in both groups as well,
which saves us the time to answer a message that already has been
replied to in another group.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Many thanks guys.
Apologies for the multi-post.
Rgds Robbie
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:21qvm1p9qfsems6v8b58m3m1e2ir0dfp47@.
4ax.com...
On Mon, 7 Nov 2005 14:45:04 -0000, Astra wrote:

>Hi All
>Wondered if you could help me with the below query.
Hi Robbie,
I answered the exact same question in comp.databases.ms-sqlserver about
an hour ago.
Please do not post the same message independantly to several groups.
Pick one group; post there. Or, if you really think that the message is
appropriate for two groups, post ONE SINGLE message and crosspost it to
both groups. That way, all answers will appear in both groups as well,
which saves us the time to answer a message that already has been
replied to in another group.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql