Mysql group-concat is giving wrong output

Question

My purchaseproduct table

+------------+------------+
| productids | quantities |
+------------+------------+
| 1,3,4,5    | 1,1,1,1    |
| 2,3,4,5    | 1,1,1,1    |
+------------+------------+

My product table

 productsid | productsname                 |
+------------+-----------------------------+
|          1 | Phone                       |
|          2 | Laptop                      |
|          3 | Charger                     |
|          4 | Earphone                    |
|          5 | Camera                      |

I want to get product name based on productids in purchaseproduct table

Like below Out put is needed

Phone,Charger,Earphone,Camera (In row one)
Laptop,Charger,Earphone,Camera (In row two)

I tried this below statement and many other

select group_concat(p.productsname) from purchaseproducts as pp join products as p on find_in_set(p.productsid,pp.productids);

But the output I get is

 Phone,Charger,Earphone,Camera,Laptop,Charger,Earphone,Camera (All in one row)

How can I achieve the output I need?

You know why you are asking this question? Because you overlooked the database design. Storing comma separated values is just too bad! [**Is storing a delimited list in a database column really that bad?**](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) **YES** — 1000111, Jul 13 '16 at 10:07

sagi · Accepted Answer · 2016-07-13T10:18:24.037

1

You can simply use DISTINCT inside the GROUP_CONCAT :

select pp.productsid , group_concat(DISTINCT p.productsname)
from purchaseproducts pp 
join products p 
 on find_in_set(p.productsid,pp.productids);
GROUP BY pp.productsid

edited Jul 13 '16 at 10:18

answered Jul 13 '16 at 10:08

sagi

40,026
6
59
84

using distinct is not working it also prints only one row but disting out put – Raja Dhasan Jul 13 '16 at 10:11
Need to use `GROUP BY`. By the way, isn't there any `PK` in `purchaseproduct` table? @RajaDhasan – 1000111 Jul 13 '16 at 10:12
Thanks working.. I used group by with a unique PK in my table – Raja Dhasan Jul 13 '16 at 10:15
If there were another row having `productsid = 1,4,3,5 ` then you would get three rows. Although you would expect to get `2` rows. That's why `CSV is bad` @RajaDhasan – 1000111 Jul 13 '16 at 10:16
@1000111 How do you know he expects to get 2 in this case? And yea, in general I highly agree.. change the DB design! – sagi Jul 13 '16 at 10:19
Isn't it obvious? Although these two strings (`1,4,3,5`, `1,3,4,5`) consist of the same `product ids` but `GROUP BY` will treat this as different. Correct me if I am wrong. @sagi – 1000111 Jul 13 '16 at 10:22
I never said you are wrong, but if he stores both of them, then I'd expect that he'd expect them as well. – sagi Jul 13 '16 at 10:22
I have a Primary key to sort it out .. I used group by with it to overcome :) – Raja Dhasan Jul 16 '16 at 10:37

Mysql group-concat is giving wrong output

1 Answers1