Need help writing query
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. 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 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
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]
Re: Need help writing query
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 | bPromenade/b Blended Knit Shirt | Casual Wear | | | 28.00 | 71080 | ?L.MYI | 71080| bLee Denim Shirt/bbr | Casual Wear | | | 24.00 | 77123 | ?L.MYI | 77123| bLee Golf Shirt/bbr | 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 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 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]
