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 3 1780
On Mon, 7 Nov 2005 14:45:41 -0000, Astra wrote: 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.
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)
)
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?
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)
Astra (No@Spam.com) writes: 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?
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, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Many thanks guys
Apologies for multi-post.
Rgds Robbie
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
Astra (No@Spam.com) writes: 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?
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, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: gekoblu |
last post by:
Hi!, I want to fix via javascript the combo width to a fix value. I'd
like to implement a kind of ALT / TITLE function to show the entire
option when the text is longer than the combo width......
|
by: Joachim Bauer |
last post by:
I'm using the code below to display a menu that opens when the mouse
goes over the main menu item (try it in your browser to understand the
behaviour).
It uses "position:absolute" and a switch...
|
by: Aaron C |
last post by:
This is kindof a newbie question, so please bear with me...
I need to pass the selected option of a <select> tag into a query
string, so that the page is reloaded with the selected option already...
|
by: Bonge Boo! |
last post by:
This has got to be obvious, but I can't make it work.
I have a form called with 3 pull down menus. They are linked to a database
which generates the values for the <SELECT? Pull-downs.
Lets...
|
by: Brian Foley |
last post by:
Hello,
I am used to using the label tag with check boxes and radio buttons in
html forms. This allows me to click on the text of the label to
activate/deactivate the check box / button, rather...
|
by: Astra |
last post by:
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...
|
by: Chris Fink |
last post by:
Does anyone know it is possible to include a small image(.gif .jpeg)
within a <SELECT><option> so that the user would see the option text as
well as a little image(icon) in the option? I know this...
|
by: frey |
last post by:
i tried to create a dropdown menu and use the option as a control to
change content inside another text area
the code is like this:
<select name="xxxx"><option onclick="changeunitprice(29.87)"...
|
by: senthilganga |
last post by:
Hello,
I use a simple list-based drop-down menu :
The problem is that there is a z-index issue in IE6 that causes the menu to be placed BEHIND <select> input items.
I understand that this...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |