Typhoon101 Typhoon101 - 16 days ago 7
MySQL Question

MYSQL Concatenate columns without grouping

I have a single table containing country codes, and language ids

+------+------+-------------+
| id | iso | language_id |
+------+------+-------------+
| 1 | US | 4 |
| 2 | IE | 1 |
| 3 | DE | 2 |
| 4 | SG | 1 |
| 5 | FR | 3 |
| 6 | UK | 1 |
| 7 | AT | 2 |
+------+------+-------------+


What I need is a MySQL statement that will return a result set containing EVERY ISO and a concatenated string of ids where the language id matches

So in the example above, I am looking to get

+------+------+----------+
| id | iso | id_group |
+------+------+----------+
| 1 | US | 4 |
| 2 | IE | 2,4,6 |
| 3 | DE | 3,7 |
| 4 | SG | 2,4,6 |
| 5 | FR | 5 |
| 6 | UK | 2,4,6 |
| 7 | AT | 3,7 |
+------+------+----------+


My best attempt so far is shown below and in the sqlfiddle link, but the grouping is excluding some of the ISO's. I need to return every row

SELECT iso, language_id, GROUP_CONCAT(id) as id
FROM countries
GROUP BY language_id


http://sqlfiddle.com/#!9/907618/3

Can this be done with MySQL or will I need to run many statements to get the results?

Thanks

Answer

This query will return all ID for every language ID:

select language_id, GROUP_CONCAT(id ORDER BY id) as id_group
from countries 
group by language_id

then you just have to join this query with the countries table:

select
  c.id,
  c.iso,
  g.id_group
from
  countries c inner join (
    select language_id, GROUP_CONCAT(id ORDER BY id) as id_group
    from countries 
    group by language_id
  ) g on c.language_id = g.language_id
order by
  c.id

Without a subquery you could use a self-join:

select
  c.id,
  c.iso,
  group_concat(c1.id order by c1.id) as id_group
from
  countries c inner join countries c1
  on c.language_id = c1.language_id
group by
  c.id,
  c.iso
Comments