Find duplicate records and give result in concat string

Question

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)

score 4 · Accepted Answer · answered Oct 18 '12 at 16:08

4

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

answered Oct 18 '12 at 16:08

John Woo

It worked, thank you very much! It was a "distinct" that I was missing in group_concat. Now it's quite obvious, but yesterday it wasn't :) – Victoria B Oct 19 '12 at 10:47