subquery multiple rows

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=gcdmg-mysql-2@m.gmane.org

Re: subquery multiple rows

On Wed, 2010-04-07 at 11:37 -0400, 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 |
> -------------------------------------------------
>

I think you want:
select prod as m, group_concat(category) as n from products group by
prod;

hth

Nigel


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: subquery multiple rows

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=nsullivan@cappex.com
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

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=nsullivan@cappex.com
>>
>>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: subquery multiple rows

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.ht ml#function_grou
p-concat

SET [GLOBAL | SESSION] group_concat_max_len = val;


Steven Staples




> -----Original Message-----
> From: kalin m [mailto:kalin@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=nsullivan@cappex.com
> >>
> >>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=sstaples@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=gcdmg-mysql-2@m.gmane.org