Display comma separated results with Mysql

Question

administrador  | regiao
bruno          | 2,3,4
pedro          | 1
jose           | 5,7

I have an administrator table and a region table, where 1 administrator can manage a region or several. I want to make a query that brings me the name of the regions that each administrator is responsible separating by commas. Ex:

administrador  | regiao
bruno          | São Paulo, Belo Horizonte, Sorocaba
pedro          | Boituva
jose           | Rio de Janeiro, Resende

How to make this consultation?

Answer 1

I made a fiddle to validate, taking advantage of the idea of group_concat from the @fernandosavio response:

select a.nomeAdm as nome,
       GROUP_CONCAT(DISTINCT r.nomeREg SEPARATOR ", ") as regiao
  from regiao r
 inner join administrador a on r.idAdm = a.idAdm
 group by r.idAdm, a.nomeAdm

Here the fiddle: http://sqlfiddle.com/#! 9/06be20/8

Answer 2

Then better understand the problem here are my thoughts and possible solutions.

Solution 1:

The best idea would be to restructure the tables with Foreign Keys. N-1 if areas can only have one administrator. N-N if an area can have multiple administrators and each administrator can manage multiple areas.

If option N-1 is chosen, the @Ricardo-punctual response is exactly what you need. Since the table regions have a foreign key for the table administrators.

Ad-hoc response for reference:

SELECT 
    a.nomeAdm as nome,
    GROUP_CONCAT(DISTINCT r.nomeREg SEPARATOR ", ") as regiao
FROM regiao r
INNER JOIN administrador a ON r.idAdm = a.idAdm
GROUP BY r.idAdm;

If N-N is the need, I keep the previous answer which is to have a pivot table with foreign keys for regions and administrators.

SELECT
    a.nome as administrador,
    GROUP_CONCAT(DISTINCT r.nome SEPARATOR ", ") as regioes
FROM administradores as a
INNER JOIN adm_regioes AS ar ON ar.adm_id = a.id
INNER JOIN regioes AS r ON ar.regiao_id = r.id
GROUP BY a.id;

Solution 2:

If it is not possible to restructure the tables, it is possible to make a Join using REGEXP so that the DBMS recognizes the field regions and can reference the table regions.

An example would be:

SELECT
    a.nome as nome,
    a.regions as regions,
    GROUP_CONCAT(DISTINCT r.nome SEPARATOR ', ') as regions_concat
FROM administrators as a
INNER JOIN regions as r
    ON a.regions REGEXP CONCAT("[[:<:]]", r.id, "[[:>:]]")
GROUP BY a.id;

Upshot:

+-------+---------+----------------------+
| nome  | regions |    regions_concat    |
+-------+---------+----------------------+
| Pedro |       1 | Sao paulo            |
| João  |     2,3 | Campinas, Manaus     |
| José  |     4,5 | Campo Grande, Santos |
+-------+---------+----------------------+

The INNER JOIN would be done by applying the REGEXP in the column where the symbols [[:<:]] and [[:>:]] are the word boundaries (equivalent to \b in other REGEXP engines).

In this way your problem is solved, but it should be pointed out that it is not ideal and also not at all performatic.

See working on Dbfiddle

PS: You need to test if it works normally with two or more digit numbers as well.

---

These are the options I see without having to deal with at the application level. I hope it helps.

Answer 3

select admin.name,
       GROUP_CONCAT(DISTINCT r.name SEPARATOR ", ") as regiao
  from regions r
 inner join administrator admin on r.id = admin.regions
 group by r.id, admin.name order by admin.id desc

The result of the above query was:

name | regiao
pedro| sao paulo
joao | manaus
jose | campo grande

When it really should be:

name | regiao
pedro| sao paulo
joao | manaus, campinas
jose | campo grande, santos

The query is not making the concatenations, only this considering the first region. Remembering that the tables are like this :

Regions
id | name
1  | Sao paulo
2  | Manaus
3  | Campinas
4  | Campo Grande
5  | Santos

Administrator
id | name | regions
1  | pedro| 1
2  | joao | 2,3
3  | jose | 4,5