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? YES — 1000111
– 1000111, Commented Jul 13, 2016 at 10:07

sagi · Accepted Answer · 2016-07-13 10:18:24Z

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, 2016 at 10:18

answered Jul 13, 2016 at 10:08

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Raja Dhasan Over a year ago

using distinct is not working it also prints only one row but disting out put

1000111 Over a year ago

Need to use GROUP BY. By the way, isn't there any PK in purchaseproduct table? @RajaDhasan

Raja Dhasan Over a year ago

Thanks working.. I used group by with a unique PK in my table

1000111 Over a year ago

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

sagi Over a year ago

@1000111 How do you know he expects to get 2 in this case? And yea, in general I highly agree.. change the DB design!

|

Collectives™ on Stack Overflow

Mysql group-concat is giving wrong output

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related