SELECT Cat_Item_Img,Cat_Price,Product.Item_Code,GROUP_CONCAT(Item_Size SEPARATOR ";") as sizes,Item_Img,
Description,Category,Retail_Price,Short_Desc,Product.Item_Color
FROM Cat_Items,Product
WHERE cat_code ="BoltTech" and Cat_Items.Item_Code = Product.Item_Code
and
Category ="Casual Wear" GROUP BY Product.Item_Code
RESULTS:
| Cat_Item_Img | Cat_Price | Item_Code | sizes | Item_Img | Description | Category |
| | 19.00 | 288 | ?L.MYI | 288 | <b>Promenade</b> Blended Knit Shirt | Casual Wear |
| | 28.00 | 71080 | ?L.MYI | 71080 | <b>Lee Denim Shirt</b><br> | Casual Wear |
| | 24.00 | 77123 | ?L.MYI | 77123 | <b>Lee Golf Shirt</b><br> | Casual Wear |
The data should read S;M;L;XL;2XL;3XL not ?L.MYI (?L.MYI is translation when using copy/paste, there were other characters in sizes field.)
Is this a 4.1 Bug? I loaded from the 4.1.0-0 Linux x86 RPM files. So far nothing else appears broken. Any suggestions?
Patrick
At 09:11 AM 9/18/2003, you wrote:
Patrick Shoaf <[EMAIL PROTECTED]> wrote:
> I have a table name product defined as follows:
> Item_Code
> Item_Size
> Item_Color
> Item_img
> Description
> Cost
> Retail_Price
> Category
> and other non-essential items, such as qty based on code,size,&color
>
> The data is as follows:
> j2400 S BLK j2400blk.jpg Black Jacket 12.00 24.00 Jacket
> j2400 M BLK j2400blk.jpg Black Jacket 12.00 24.00 Jacket
> j2400 L BLK j2400blk.jpg Black Jacket 12.00 24.00 Jacket
> j2400 4XL BLK j2400blk.jpg Black Jacket 18.00 36.00 Jacket
> p2400 S BLK p2400blk.jpg Black Pants 16.00 32.00 Pants
> p2400 M BLK p2400blk.jpg Black Pants 16.00 32.00 Pants
> p2400 L BLK p2400blk.jpg Black Pants 16.00 32.00 Pants
> p2400 4XL BLK p2400blk.jpg Black Pants 24.00 48.00 Pants
>
> I need the result to show:
> j2400 S,M,L,4XL BLK j2400blk.jpg Black Jacket 12,12,12,18
> p2400 S,M,L,4XL BLK p2400blk.jpg Black Pants 16,16,16,24
>
> How would you write this query?
> I used initially
>
> SELECT Item_Code,Item_Size,Item_Color,Item_Img,Description,Cost FROM product
> WHERE Category="Jacket" or Category="Pants" GROUP BY Item_code
>
> This resulted in only retrieving the first item in the list for size &
> price info.
>
There is a GROUP_CONCAT() function, that is available from version 4.1: http://www.mysql.com/doc/en/GROUP-BY-Functions.html
-- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Patrick J. Shoaf, Systems Engineer <mailto:[EMAIL PROTECTED]>[EMAIL PROTECTED]
Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or 888-638-6963 Fax: 724-489-4386
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
