From: Steven Staples (sstaplesmnsi.net)
Date: Mon Apr 12 2010 - 08:39:34 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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:kalinel.net]
> Sent: April 7, 2010 12:59 PM
> To: Nathan Sullivan
> Cc: mysqllists.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=nsullivancappex.com
> >>
> >>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=sstaplesmnsi.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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]