Make a GROUP_CONCAT to row groups without repeating the grouped rows induvidually

Question

I am trying to GROUP_CONCAT some results that are already grouped, but I'm getting concatenated the previously grouped rows.

Let's say I have a Table of Persons. Each person has a list of products they bought.

Customer:

+-------+---------+
| name  | comment |
+-------+---------+
| David | Hey!    |
| Joe   | Bye!    |
| John  | Hey!    |
+-------+---------+

Product:

+-------+----------+----------+
| name  | customer | category |
+-------+----------+----------+
| Apple | David    | Food     |
| Melon | David    | Food     |
| Pizza | John     | Food     |
| Pizza | David    | Food     |
| Water | Joe      | Drink    |
+-------+----------+----------+

I need to get the comments of all customers that bought food or drink.

So I JOIN the tables and I get:

+--------------+---------------+------------------+------------------+
| product.name | customer.name | customer.comment | product.category |
+--------------+---------------+------------------+------------------+
| Apple        | David         | Hey!             | Food             |
| Melon        | David         | Hey!             | Food             |
| Pizza        | John          | Hey!             | Food             |
| Pizza        | David         | Hey!             | Food             |
| Water        | Joe           | Bye!             | Drink            |
+--------------+---------------+------------------+------------------+

Now, to get rid of duplicate customers I make a GROUP BY customer.name and I get:

+--------------+---------------+------------------+------------------+
| product.name | customer.name | customer.comment | product.category |
+--------------+---------------+------------------+------------------+
| Apple        | David         | Hey!             | Food             |
| Pizza        | John          | Hey!             | Food             |
| Water        | Joe           | Bye!             | Drink            |
+--------------+---------------+------------------+------------------+

At this point, I expect to make a GROUP BY category, concatenating the comments.

SELECT GROUP_CONCAT(customer.comment) AS 'comments' FROM Customer c JOIN Product p ON c.name = p.name group by product.category.

This query returns:

+----------------+------------------+
|    comments    | product.category |
+----------------+------------------+
| Hey!,Hey!,Hey! | Food             |
| Bye!           | Drink            |
+----------------+------------------+

As you can see, the "Hey!" of David is duplicated, what I expect is this result:

+-----------+------------------+
| comments  | product.category |
+-----------+------------------+
| Hey!,Hey! | Food             |
| Bye!      | Drink            |
+-----------+------------------+

Note that David and John have the same comment text. I did this in purpose because I don't want to fix this duplicity with a distinct, as I do want repeated comments if they come from different customers.

Is there a way to apply the concat AFTER the grouping without subquerying? I feel like it is using the comments before they were grouped by customer name...

Answer 1

Lets consider the query as your_query which you have mentioned as

Now, to get rid of duplicate customers I make a GROUP BY customer.name and I get

Then use it in from subquery as follows:

Select SELECT GROUP_CONCAT(comment) AS 'comments', category
  FROM (your_query) t
 Group by category;