Is it possible to obtain multi-valued results in a group by?

Question

I have 3 tables in the bank: Teacher, Student and Discipline. All of them have N:M relationships with each other. Then decide to gather all relationships in a single class table, which stores the foreign key of the 3 tables.

To assemble the page I need to return from the bank something similar to this structure:

Object(Class)[6]
-- private 'class' => string 'Banks A239812' (length=15)
-- private 'dtIni' => string '2012-01-20' (length=10)
-- private 'dtFim' => string '2012-06-20' (length=10)
-- private 'turn' => string 'morning' (length=8)
-- private 'n1' => string '' (length=0)
-- private 'N2' => string '' (length=0)
-- private 'N3' => string '' (length=0)
-- private 'professor' => string 'Bruno Faria' (length=11)
-- private 'student' =>
---- array (size=2)
------ 0 =>
--------- 'name' => string 'Amanda dos Santos' (length=17)
------ 1 =>
-------- 'name' => string 'Nalva de Souza Sá' (length=18)
-- private 'disciplina' => string 'Database II' (length=17)

To get this result I can do with two searches:

SELECT t.turma AS turma, p.nome AS professor, d.nome AS disciplina, t.dt_ini AS dtIni, t.dt_fim AS dtFim, t.turno AS turno  
FROM turma AS t 
INNER JOIN professor AS p 
INNER JOIN disciplina AS d 
ON t.turma COLLATE utf8_general_ci = busca_banco AND p.id = t.pk_professor AND  d.id = t.pk_disciplina 
GROUP BY t.turma

SELECT a.nome 
FROM turma AS t 
INNER JOIN aluno AS a 
ON t.pk_aluno = a.id AND t.turma COLLATE utf8_general_ci = turma AND t.dt_ini = data_ini AND t.dt_fim = data_Fim 
ORDER BY a.nome

I wonder if it is possible to do it in a single query. This query should do a search by class name and group the results (GROUP BY).

But in the same query I want to return the name of all students who are taking the course.

So I have a grouping, but I would like to get several tuples for one of the fields. This is possible?

Answer 1

Since the number and types of the fields of the two querys are different, you cannot do a single search, even with the UNION. The best way to gather this data is to query the name of the students in the class and another to return the other items. As follows:

Query 1

SELECT 
    t.turma AS turma,
    p.nome AS professor,
    d.nome AS disciplina,
    t.dt_ini AS dtIni,
    t.dt_fim AS dtFim,
    t.turno AS turno
FROM
    turma AS t
        INNER JOIN
    professor AS p
        INNER JOIN
    disciplina AS d ON t.turma COLLATE utf8_general_ci = busca_banco
        AND p.id = t.pk_professor
        AND d.id = t.pk_disciplina
GROUP BY t.turma

Query 2

SELECT 
    a.nome
FROM
    turma AS t
        INNER JOIN
    aluno AS a ON t.pk_aluno = a.id
        AND t.turma COLLATE utf8_general_ci = turma
        AND t.dt_ini = data_ini
        AND t.dt_fim = data_Fim
ORDER BY a.nome