Display comma-separated results with MySQL

After a better understanding of 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 the areas can only have one administrator. N-N if an area can have multiple administrators and each administrator can manage multiple areas.

If N-1 is chosen, the reply from @ ricardo-puntual is exactly what you need. As long as the regions table has a foreign key for the administrators table.

@ ricardo-puntual's 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 that 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 you can not restructure tables, you can join using REGEXP so that the DBMS recognizes the regions field and is able to reference the regions table.

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;

Result :

+-------+---------+----------------------+
| 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 to REGEXP in the column where the% and% of% are the word boundaries (equivalent to [[:<:]] in other REGEXP engines).

In this way your problem is solved, but it should be emphasized that it is not ideal and also not a little performative.

See working in DBFiddle

PS: You need to test if it works normally with numbers of two or more digits too .

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

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

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's the fiddle: link

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 in fact it should be:

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

The query is not doing the concatenations, it is only considering the first region. Remembering that the tables look 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