Re: Need help writing query

[Patrick Shoaf]

I loaded MySQL 4.1, but now the text is gibberish in the Group_Concat field

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  | Promenade Blended Knit 
Shirt 
| Casual Wear |
|  | 28.00 | 71080 | 
?L.MYI   | 
71080| Lee Denim 
Shirt 
| Casual Wear |
|  | 24.00 | 77123 | 
?L.MYI   | 
77123| Lee Golf 
Shirt 
| 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.jpgBlack 
Jacket12.00   24.00   Jacket
> j2400   M   BLK j2400blk.jpgBlack 
Jacket12.00   24.00   Jacket
> j2400   L   BLK j2400blk.jpgBlack 
Jacket12.00   24.00   Jacket
> j2400   4XL BLK j2400blk.jpgBlack 
Jacket18.00   36.00   Jacket
> p2400   S   BLK p2400blk.jpgBlack 
Pants 16.00   32.00   Pants
> p2400   M   BLK p2400blk.jpgBlack 
Pants 16.00   32.00   Pants
> p2400   L   BLK p2400blk.jpgBlack 
Pants 16.00   32.00   Pants
> p2400   4XL BLK p2400blk.jpgBlack 
Pants 24.00   48.00   Pants
>
> I need the result to show:
> j2400   S,M,L,4XL   BLK j2400blk.jpgBlack Jacket12,12,12,18
> p2400   S,M,L,4XL   BLK p2400blk.jpgBlack 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
[EMAIL PROTECTED]
Midmon Internet Services, LLC
100 Third Street
Charleroi, PA 15022
http://www.midmon.com
Phone: 724-483-2400 ext. 105
 or888-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]

Re: Need help writing query

[Egor Egorov]

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.jpgBlack Jacket12.00   24.00   Jacket
> j2400   M   BLK j2400blk.jpgBlack Jacket12.00   24.00   Jacket
> j2400   L   BLK j2400blk.jpgBlack Jacket12.00   24.00   Jacket
> j2400   4XL BLK j2400blk.jpgBlack Jacket18.00   36.00   Jacket
> p2400   S   BLK p2400blk.jpgBlack Pants 16.00   32.00   Pants
> p2400   M   BLK p2400blk.jpgBlack Pants 16.00   32.00   Pants
> p2400   L   BLK p2400blk.jpgBlack Pants 16.00   32.00   Pants
> p2400   4XL BLK p2400blk.jpgBlack Pants 24.00   48.00   Pants
> 
> I need the result to show:
> j2400   S,M,L,4XL   BLK j2400blk.jpgBlack Jacket12,12,12,18
> p2400   S,M,L,4XL   BLK p2400blk.jpgBlack 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]