If i may add (and I am no expert), but just be careful of how much you're group_concat does, as there is a group_concat_max_len value (you can override it though). I have run into this once, and couldn't figure out why i wasn't getting all my data.
-- taken from the mysql site: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_grou p-concat SET [GLOBAL | SESSION] group_concat_max_len = val; Steven Staples > -----Original Message----- > From: kalin m [mailto:ka...@el.net] > Sent: April 7, 2010 12:59 PM > To: Nathan Sullivan > Cc: mysql@lists.mysql.com > Subject: Re: subquery multiple rows > > > > > yea.. almost. but it helped a lot. now i know about those functions > too. thank you... > > > Nathan Sullivan wrote: > > I think you want to do something like this: > > > > select prod, group_concat(category separator ', ') > > from products > > group by prod; > > > > > > Hope this helps. > > > > On Wed, Apr 07, 2010 at 08:37:04AM -0700, kalin m wrote: > > > >> hi all... > >> > >> i have a bit of a problem with this: > >> > >> table products: > >> > >> ---------------------- > >> prod | category | > >> ---------------------| > >> boots | winter | > >> boots | summer | > >> boots | spring | > >> shoes | spring | > >> shoes | winter | > >> shoes | fall | > >> shoes | summer | > >> ---------------------- > >> > >> when i do this: > >> > select distinct prod as m, (select category from products where > email > >> = m) as n from products; > >> > >> i get: > >> > >> ERROR 1242 (21000): Subquery returns more than 1 row > >> > >> i know that the subquery returns more than one rows. i hope so... > >> > >> what i'd like to see as result is: > >> > >> ------------------------------------------------- > >> m | n | > >> ------------------------------------------------- > >> boots | winter, summer, spring | > >> shoes | spring, winter, fall , summer | > >> ------------------------------------------------- > >> > >> > >> or at least: > >> > >> --------------- > >> m | n | > >> --------------- > >> boots | 3 | > >> shoes | 4 | > >> ---------------- > >> > >> > >> > >> > >> thanks.... > >> > >> > >> > >> > >> > >> -- > >> MySQL General Mailing List > >> For list archives: http://lists.mysql.com/mysql > >> To unsubscribe: > http://lists.mysql.com/mysql?unsub=nsulli...@cappex.com > >> > >> > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net > > No virus found in this incoming message. > Checked by AVG - www.avg.com > Version: 9.0.791 / Virus Database: 271.1.1/2783 - Release Date: > 04/07/10 02:32:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
