Is it possible to get multivalued results in a group by?

Question

Is it possible to get multivalued results in a group by?

Navigation

#1 by (0 votes)

1

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

To mount the page I need to return from the bank something like this structure:

object (Turma) [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 'shift' = > string 'matutino' (length = 8)
  - private 'n1' = > string '' (length = 0)
  - private 'n2' = > string '' (length = 0)
  - private 'n3' = > string '' (length = 0)
  - private 'teacher' = > string 'Bruno Faria' (length = 11)
  - private 'student' = > Home     ---- array (size = 2)
    ------ 0 = > Home     --------- 'name' = > string 'Amanda dos Santos' (length = 17)
    ------ 1 = > Home     -------- 'name' = > string 'Nalva de Souza Sá' (length = 18)
  - private 'discipline' = > 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's possible to do it in a single query. This query should search the 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. Is this possible?

asked by anonymous 31.05.2016 / 16:28

1 answer

Difficulties with css element positioning Buttons in the mobile version do not work

score 0 · Answer 1

Since the number and types of fields of the two querys are different, you can not do a single search, even with UNION. The best way for you to merge this data is to do a query for 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