Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

On my website I'm building an activity feed where I got the basics done and are now making some improvements here and there.

One of them is the grouping. I've built it so that if multiple users buys the same 2 or more products the activity feed shows it as one activity: "user1 & user2 bought product 1, 2 & 3".

The problem is that if user1 buys product 1 & 2 and user2 buys product 1, 2 & 3 it appears as two different activities: "user1 bought product 1 & 2" AND "user2 bought product 1, 2 & 3".

I would like it to show as "user1 & user2 bought product 1 & 2" and then another saying "user2 bought product 3"

I've made a simplified sqlfiddle of what I've got so far. Try changing it by deleting NULL, 'Bought', 1, 3

Is this something that is possible to do in MySQL or should I do it in PHP?

share|improve this question
up vote 2 down vote accepted

You need to group users by activity and product in the first step, and then group products by the groups of users.
I use ORDER BY with GROUP_CONCAT(), so that the aggregated usernames are concatenated in the same order.

SELECT x.usernames
      ,GROUP_CONCAT(p.product_name ORDER BY p.product_name) AS product_names
FROM   products p
   SELECT a.activity
         ,GROUP_CONCAT( ORDER BY AS usernames
   FROM   activities a
   LEFT   JOIN users u ON = a.userId
   GROUP  BY 1, 2
   ) x ON x.productId =
GROUP  BY 1, 2
ORDER  BY 1, 2;

-> sqlfiddle


John       | Bought     | The Dark Knight Rises
John,Peter | Bought     | Batman Begins,The Dark Knight
share|improve this answer
Amazing, thanks! :) – Oskar Jan 7 '13 at 12:44
@Oskar, do you run additional mysql queries through PHP, when you parse the concatenated result, e.g. usernames from Erwin's sqlfiddle?. – John Smith Dec 16 '15 at 11:47

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.