Ati Que Timporta Ati Que Timporta - 9 months ago 27
MySQL Question

How Can I show a field of sql consult with comma separation and without repeat fields

I have this tables:

TABLE tema

+---------+-------------+
| tema_id | nombre |
+---------+-------------+
| 1 | Aprender |
| 4 | Justicia |
| 2 | Pensamiento |
| 3 | Vida |
+---------+-------------+


TABLE frase

+----+----------+----------------------------------------+
| id | autor_id | texto |
+----+----------+----------------------------------------+
| 1 | 2 | Pienso, luego existo |
| 2 | 1 | Aprender sin pensar es trabajo perdido |
+----+----------+----------------------------------------+


TABLE tema_frase (relation with table frase and tema)

+----------+---------+
| frase_id | tema_id |
+----------+---------+
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
+----------+---------+


So when I perform this consult:

SELECT frase.texto, tema.nombre
FROM tema_frase INNER JOIN
tema
ON tema_frase.tema_id = tema.tema_id INNER JOIN
frase
ON frase.id = tema_frase.frase_id


I get this:

+----------------------------------------+-------------+
| texto | nombre |
+----------------------------------------+-------------+
| Pienso, luego existo | Pensamiento |
| Aprender sin pensar es trabajo perdido | Aprender |
| Aprender sin pensar es trabajo perdido | Pensamiento |
+----------------------------------------+-------------+


My question is, how can I show a consult in this way?

+----------------------------------------+-----------------------+
| texto | nombre |
+----------------------------------------+-----------------------+
| Pienso, luego existo | Pensamiento |
| Aprender sin pensar es trabajo perdido | Pensamiento, Aprender |
+----------------------------------------+-----------------------+

Psi Psi
Answer Source

You would want to group the result and use group_concat to concatenate the names:

SELECT frase.texto,group_concat(tema.nombre separator ", ")
FROM tema_frase 
INNER JOIN tema 
ON  tema_frase.tema_id=tema.tema_id 
INNER JOIN frase 
ON frase.id=tema_frase.frase_id
GROUP BY frase.texto

You can also order alphabetically and avoid duplicates using group_concat:

SELECT frase.texto,group_concat(DISTINCT tema.nombre separator ", " ORDER BY tema.nombre ASC)
FROM tema_frase 
INNER JOIN tema 
ON  tema_frase.tema_id=tema.tema_id 
INNER JOIN frase 
ON frase.id=tema_frase.frase_id
GROUP BY frase.texto