How to put two records from one table into another as one in select Mysql Database

Question

How to put two records from one table into another as one in select Mysql Database?

SELECT TURMA.turma_id, TURMA.turma_nome, PROF.prof_name 
from turma TURMA LEFT JOIN professor_materia PROF_MAT 
ON TURMA.turma_id=PROF_MAT.prom_mat LEFT JOIN professor PROF 
ON PROF_MAT.prom_prof=PROF.prof_id

he’s returning like this:

I wish you’d turn around like this:

turma_id   |  turma_nome     |  prof_name
1          |  1º ANO A       |  PROFESSOR DE MATEMATICA, DANIEL
2          |  2º ANO         |  NULL

Thanks in advance.

Answer 1

In this case just use one GROUP BY with the function GROUP_CONCAT, ex:

SELECT
    MIN(TURMA.turma_id) AS turma_id,
    TURMA.turma_nome,
    GROUP_CONCAT(PROF.prof_name SEPARATOR ', ') AS prof_name
FROM
    turma TURMA
    LEFT JOIN professor_materia PROF_MAT ON TURMA.turma_id = PROF_MAT.prom_mat
    LEFT JOIN professor PROF ON PROF_MAT.prom_prof=PROF.prof_id
GROUP BY
    TURMA.turma_nome

The GROUP BY groups the result and the GROUP_CONCAT concatenates the grouped results of a column.

To read more about GROUP BY and GROUP_CONCAT: https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-Concat