If it's not too late, may I suggest that you re-structure your database
while using normalization & RDB rules???
The database that you are looking for is actually very straight-forward and
I'm sure it's been done many times before. To get you started, think about
how you would see these products in a catalog:
Item Description: Basic T-Shirt
Available Styles:
Medium - White - 15.99
Medium - Black - 15.99
Large - White - 16.99
Large - Black - 16.99
I can see at least 4 different tables all related to each other in one way
or another. In the end, this will make your queries significantly easier to
formulate and would almost certainly improve your database performance than
with what you currently have.
Good Luck,
Dennis
**********************************************
Beridney Computer Services
[EMAIL PROTECTED]
http://www.beridney.com
----- Original Message -----
From: "Weevil" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, May 24, 2001 4:19 PM
Subject: Mysql difficult query. Need help!
> Hi.
>
> I'm having some problems with formulating a SELECT statement to do the
> following:
>
> I have a database with one column of names with certain tags bounded by
> square brackets, and a price column as well. The database looks something
> like this:
>
> ITEM PRICE
>
> Shirt [Large] [Polyester] 12.99
> [Small] Pants [CategoryB] 15.99
> Sweater [A GRADE] [Medium] 20.00
> Tie [Large] [CategoryB] 5.99
>
> Tags (the stuff between the [ and ]) are not standardised and can contain
any
> string. What I want to do is find the total price for each tag and return
the
> whole thing in a report grouped by tag like this:
>
> ITEM TAG: TOTAL PRICE:
> [Large] 28.98
> [CategoryB] 21.98
> [Medium] 20.00
> [Small] 15.99
> [Polyester] 12.99
>
> I was using this SQL statement:
>
> select
> substring(Item,instr(Item,\"[\"),instr(Item,\"]\")-instr(Item,\"[\")+1) as
> ItemTag,sum(Price) as TotalPrice from Products group by ItemTag order by
> TotalPrice desc
>
> As you can see this is rather byzantine and only groups by the first tag
it
> encounters. I wondered if it was possible to apply it for as many tags as
> there are in the item name (as in the example above).
>
> I hope someone can help, I'm reasonably experienced with MySQL but this
one
> is out of my league.
>
> Thanks for any assistance you can give.
>
> --
> Steve Pick
> [EMAIL PROTECTED]
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
