CODEWITHSUNDEEP
mysql
Aman Kumar Sinha
Aman Kumar Sinha

Reputation: 427

How to concatenate rows when joining two tables

I have tow tables and I am executing a join statement on them . When i do there's multiple columns having same key and different values. Is there any way to concatenate these values separated by commas?

This is the join query 

SELECT A.bucket_id,B.id FROM coms_category A JOIN product_category B ON A.category_url=B.url;

And a sample of a result is like this

+-----------+-----+
| bucket_id | id  |
+-----------+-----+
|      1261 | 692 |
|      1157 | 600 |
|      1222 | 600 |

As 600 comes twice . My desired output is 1157,1222 against 600 .

I have tried 

SELECT B.id,A.bucket_id GROUP_CONCAT(A.bucket_id SEPARATOR ', ')FROM coms_category A JOIN product_category B ON A.category_url=B.url GROUP BY B.id;

but it didn't work .

Upvotes: 0

Views: 40

Answers (1)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

use sub-query ,1st group by will remove duplication 

select T.id ,GROUP_CONCAT(T.bucket_id SEPARATOR ', ')
 from (
  SELECT B.id,A.bucket_id 
    FROM coms_category A 
    JOIN product_category B 
    ON A.category_url=B.url 
    GROUP BY B.id,A.bucket_id
) as T group by T.id

Upvotes: 1

Related Questions