Search code examples
mysqlinner-join

MySQL join two tables to produce column with comma separated values

I have a mysql problem, and maybe the title is a little confusing, but here goes:

I have two tables

    TABLE A           TABLE B
+-----+--------+   +-----+--------+
| id  | NumCol |   | id  | NumCol |
+-----+--------+   +-----+--------+
| 101 |  123   |   | 101 |        |
| 101 |  124   |   +-----+--------+  
+-----+--------+

Now I'd like to join these two on the id, so I get an output like this

+-----+--------+
| id  | NumCol |
+-----+--------+
| 101 | 123,124|
+-----+--------+

Anyone know of a handy way to do this?

Solution

  • Try this query

    SELECT tb.id,GROUP_CONCAT(ta.NumCol) AS NumCol FROM tableB AS tb
JOIN tableA AS ta ON ta.id=tb.id
GROUP BY tb.id