MySQL Group Concat one cell when other cells also match

database-designgroup-concatenationMySQL

I'm trying to concatenate the content of a single cell when two cells from other colums also match. For example, say I have a table (including joins) that looks like this:

+------------+--------------+-------------+--------------+
| sale_id    | product_no   | date_sold   | shift_start  |
+------------+--------------+-------------+--------------+
|          1 |          123 | 2018-01-21  | 10:00        |
|          2 |          456 | 2018-01-22  | 18:00        |
|          3 |          123 | 2018-01-22  | 18:00        |
|          4 |          123 | 2018-01-23  | 02:00        |
|          5 |          789 | 2018-01-24  | 02:00        |
+------------+--------------+-------------+--------------+

What I'm trying to achieve is concatenating the product numbers into a comma-separated string ONLY where date_sold and shift_start also match (such as the case of sale_id 2 and 3. Like this:

+------------+-------------+-------------+
| product_no | date_sold   | shift_start |
+------------+-------------+-------------+
|        123 | 2018-01-21  | 10:00       |
|   456, 123 | 2018-01-22  | 18:00       |
|        123 | 2018-01-23  | 02:00       |
|        789 | 2018-01-24  | 02:00       |
+------------+-------------+-------------+

I've tried using Group Concat but it just groups all rows into a single row. I still want to maintain rows that don't have a matchting date and shift. Cheers.

Best Answer

You need to divide the rows into groups using GROUP BY. Something like:

select group_concat(product_no), date_sold, shift_start 
from sales 
group by date_sold, shift_start;

I assume you did something like:

select group_concat(product_no), date_sold, shift_start 
from sales

The latter is invalid in most (all but MySQL?) DBMS products, but MySQL allows it by default.

Related Question