Doubt on using LEFT JOIN in mysql

Question

Doubt on using LEFT JOIN in mysql

Navigation

#1 by (3 votes)

7

I have the following problem:

I have two tables. students and proof

Students have 10 students evidence has 9 results (because one student was absent)

The union of the tables occurs through the enrollment field.

I want to merge the two tables and display all the students (including what was missing). In research I saw that using LEFT JOIN would do it.

I'm getting display. But the missing student does not show up.

My query looks like this:

SELECT alunos.*, provas.* FROM alunos 
LEFT JOIN provas ON alunos.matricula = provas.aluno_matricula 
WHERE provas.cod_prova = '00112233'
ORDER BY alunos.nome ASC

This type of query would agree to display the name of the student that has in the table students and does not have in the notes table?

Thank you!

php mysql join

asked by anonymous 22.05.2016 / 17:12

1 answer

Group by with subdivisions Disable download button HTML5 Video

score 3 · Answer 1

In this case, you will return all records in the student table, even if they are not in the test table, but since you are filtering data from the test table, you should also consider the null value, because when there is no no corresponding record, the value will be null, for example:

SELECT alunos.*, provas.* FROM alunos 
LEFT JOIN provas ON alunos.matricula = provas.aluno_matricula 
WHERE provas.cod_prova = '00112233' or provas.cod_prova is null
ORDER BY alunos.nome ASC

Doing this way, even if there are no corresponding records in the test table, will bring.