List:General Discussion« Previous MessageNext Message »
From:Steven Staples Date:April 12 2010 1:39pm
Subject:RE: subquery multiple rows
View as plain text  
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:kalin@stripped]
> Sent: April 7, 2010 12:59 PM
> To: Nathan Sullivan
> Cc: mysql@stripped
> 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=1
> >>
> >>
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 
> 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

Thread
subquery multiple rowskalin m7 Apr
  • Re: subquery multiple rowsnwood7 Apr
  • Re: subquery multiple rowsNathan Sullivan7 Apr
    • Re: subquery multiple rowskalin m7 Apr
      • RE: subquery multiple rowsSteven Staples12 Apr