Group in mysql along with join

Question

Group in mysql along with join

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have a student table and a parent table. A student may be associated with up to three parents.

SELECT 
      a.idAluno,
      a.matriculaAluno,
      a.nomeAluno,
      a.sexoAluno,
      a.dataNascAluno,
      b.id,
      b.matricula,
      b.cpf,
      b.nome 
    FROM 
      alunos a 
      LEFT JOIN matriculas b 
      ON a.matriculaAluno = b.matricula 
    WHERE 
      a.status = 1 AND b.status = 1 
     GROUP by 
        b.cpf

The return is: I wish you would not repeat the same records from the first table. And I want the table matriculas to come in grouped search.

In other words, for each student record, the three records of the responsible person are grouped, since each student can have a maximum of 3 people.

php mysql join

asked by anonymous 20.04.2017 / 22:06

2 answers

Function Change product How to install version 6.10.3 of the nodejs without being through the terminal

score 0 · Answer 1

In order not to repeat the first column, you need a group by of idAluno and take the group by of cpf. Usually cpfs are unique and it does not make much sense to a group by them but you would lose the different cpfs when giving a group by in idAluno what seems wrong to me is these values because ids equal to different students does not make sense. Finally to bring the enrollment would just do in the select a * .matricula

SELECT 
      b.*,
      a.idAluno,
      a.matriculaAluno,
      a.nomeAluno,
      a.sexoAluno,
      a.dataNascAluno,
      b.id,
      b.matricula,
      b.cpf,
      b.nome 
    FROM 
      alunos a 
      LEFT JOIN matriculas b 
      ON a.matriculaAluno = b.matricula 
    WHERE 
      a.status = 1 AND b.status = 1 
     GROUP by 
        a.idAluno

score 0 · Answer 2

The problem of grouping by IdAluno is that the return will be only the first, ignoring others responsible. The fact is that it is not a problem to repeat the first information in the table, and the others will come different, just ignore them in the treatment of information. Your select is already right.