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
SQL Query - Grouping [Solved - GROUP_CONCAT]
Moderator: General Moderators
SQL Query - Grouping [Solved - GROUP_CONCAT]
Last edited by christh on Wed Mar 09, 2011 4:07 am, edited 1 time in total.
Re: SQL Query - Grouping
Try JOINing the product table first:
Then explode by # 
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.clientThere are 10 types of people in this world, those who understand binary and those who don't
Re: SQL Query - Grouping
Another way to do it is PHP processing.
SQL query:
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.
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.clientThere are 10 types of people in this world, those who understand binary and those who don't
Re: SQL Query - Grouping
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
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]
It's part of the aggregate functions documentation - http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html