MYSQL Concatenate columns without grouping

Question

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

Personally, I wouldn't do the concatenation part in MySQL but, either way, it can be very easily accomplished. It's just a JOIN. — Strawberry
– Strawberry, Commented Nov 18, 2016 at 10:47
Can you please elaborate. As I said, this was my best attempt. Thanks — BobbyP
– BobbyP, Commented Nov 18, 2016 at 10:49
Your best attempt doesn't have a join. Try with a JOIN. It's almost impossible that you could fail to solve this. — Strawberry
– Strawberry, Commented Nov 18, 2016 at 10:52
It may be impossible for you to solve, but mysql is very new to me. I spent nearly two hours just coming up with the statement above. I think it is fair to say I am not going to progress any more on this without help - which is why I came to a forum where you ask for help, not motivation. — BobbyP
– BobbyP, Commented Nov 18, 2016 at 10:58

fthiella · Accepted Answer · 2016-11-18 11:04:34Z

2

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

edited Nov 18, 2016 at 11:04

answered Nov 18, 2016 at 10:59

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Strawberry Over a year ago

There's no need for a subquery here. A JOIN will suffice.

fthiella Over a year ago

@Strawberry yes it is true, but I think a subquery is clearer and easier to understand

Strawberry Over a year ago

Perhaps we can agree to disagree.

BobbyP Over a year ago

In the absence of any useful examples with joins, I will accept this answer. It does exactly what I need. Thanks @fthiella

Collectives™ on Stack Overflow

MYSQL Concatenate columns without grouping

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related