CODEWITHSUNDEEP
mysqlconcatenation
BobbyP
BobbyP

Reputation: 2245

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

Upvotes: 0

Views: 1313

Answers (1)

fthiella
fthiella

Reputation: 49049

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

Upvotes: 2

Related Questions