Search code examples
mysqlsqlgroup-concat

Find duplicate records and give result in concat string

I have 2 tables where some of the records will be identical (except for the id)

Table A:

id, numA, codeA
6, 34, aa 
7, 34, bb 
8, 567, bc

Table B 

id, numB, codeB 
1, 34, aa 
2, 34, bb 
3, 567, bc

I need to run a query on Table B which will check if given combination of num and code exists in Table A and will give the result in such format: 

num, concat code
34, (aa,bb) 
567, (bc)
Solution

  • Join the two tables and use GROUP_CONCAT

    SELECT  a.NumA, GROUP_CONCAT(DISTINCT b.codeB)
FROM    table1 A
        INNER JOIN table2 b
          on a.numA = b.numB
GROUP BY a.NumA

    SQLFiddle Demo