SQL Query - Grouping [Solved - GROUP_CONCAT]
Posted: Tue Mar 08, 2011 3:29 am
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
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