1

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.

0

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.

| improve this answer | |
  • This did the trick. I tried GROUP BY but it didn't give me the same result (although I did try with just one column and must have done something wrong). However, yours did so thank you very much for your help. – Alluziion Jan 24 '18 at 15:08
  • If you don't specify a full group, MySQL will randomly pick rows from each group. It's a "feature" that has caused a lot of confusion over the years. – Lennart Jan 24 '18 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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