Page 1 of 1

SQL Query - Grouping [Solved - GROUP_CONCAT]

Posted: Tue Mar 08, 2011 3:29 am
by christh
Hi

I hope you can give me some guidance on an sql query I want to write - I can do everything else I mention, just not sure where to start with the query.

If I have a table that looks like this

client | product id ------------------>Joined to a product info table
---------------------------
a@a | 12
b@b | 22
a@a | 19
a@a | 08
c@c | 12
b@b | 03

I want to run a query at the end of the day, sending an email giving a summary on subscribed products but I want to send only one email per client i.e.

a@a would receive a mail on products 12,19,08 rather than 3 emails emails. As the tables are joined, storing the info as below wouldn't help much

client | product id ------------------>Joined to a product info table
---------------------------
a@a | 12,19,08

My first thoughts are to use a SELECT DISTINCT on the email address for the first query and then query the table a second time looping those results to send the email, but that would appear to be quite a long way round to achieve the results - same as if I structure as example 2 and explode the product id (certainly given that one day the table may be quite large).

I though GROUP BY would hold the answer but I can only get it to total up the product id and DISTINCT will only return one email address.

Any ideas?

Thanks

Chris

Re: SQL Query - Grouping

Posted: Tue Mar 08, 2011 4:27 am
by VladSun
Try JOINing the product table first:

Code: Select all

SELECT 
    orders.client,
    GROUP_CONCAT(products.name, SEPARATOR '#') as product_names,
    GROUP_CONCAT(products.description, SEPARATOR '#') as product_descriptions,
    GROUP_CONCAT(products.price, SEPARATOR '#') as product_prices
from 
    orders
INNER JOIN 
    products on products.id = orders.product_id
GROUP BY
    orders.client
Then explode by # :)

Re: SQL Query - Grouping

Posted: Tue Mar 08, 2011 6:22 am
by VladSun
Another way to do it is PHP processing.

SQL query:

Code: Select all

SELECT 
    orders.client,
    products.name,
    products.description,
    products.price
FROM 
    orders
INNER JOIN 
    products ON products.id = orders.product_id
ORDER BY
    orders.client
Then you have to iterate through the result set watching for $row['client'] value changes and collecting the product list. On every $row['client'] change you format and send email, reset the product list and continue looping through the DB result.

Re: SQL Query - Grouping

Posted: Wed Mar 09, 2011 4:06 am
by christh
Hey thanks ViadSun

I have never come across GROUP_CONCAT before which is just what I need.

I suppose it must be well documented, but I'm sure I never saw it when reading up on GROUP BY, ORDER BY.

Anyway thanks again

Chris

Re: SQL Query - Grouping [Solved - GROUP_CONCAT]

Posted: Wed Mar 09, 2011 4:42 am
by Eran
It's part of the aggregate functions documentation - http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html