RE: subquery multiple rows

2010-04-12 Thread Steven Staples 
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

Re: subquery multiple rows

2010-04-07 Thread nwood 
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=arch...@jab.org

Re: subquery multiple rows

2010-04-07 Thread Nathan Sullivan 
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=arch...@jab.org

Re: subquery multiple rows

2010-04-07 Thread kalin m 




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=arch...@jab.org