Postgresql query bringing more results than expected

Question

Hello, I have a situation in a system where I need to find out which student enrollments are related to a tutor in a class.

The tables I have are:

• matricula containing foreign key with student (mat_aln_id) and with class (mat_tur_id)
• professor_turma containing foreign key with tutor (prt_pes_id) and class (prt_tur_id)
• aluno_tutor which contains foreign key with teacher & class (alt_prt_id) and student (alt_pes_id)

In a class of 11 students, where only 8 are related to a specific tutor, I try to get their enrollment as follows:

SELECT DISTINCT(matricula.mat_id)
FROM matricula
INNER JOIN professor_turma ON prt_tur_id = mat_tur_id
INNER JOIN aluno_tutor ON prt_id = alt_prt_id
WHERE prt_tur_id = 92 AND prt_pes_id = 79

Expecting to receive 8 results, but I get all 11 enrolled.

The example in SQL Fiddle is here, to facilitate understanding in practice.

Can someone help me?

Answer 1

From what I saw the mistake is in the second join, the selected fields are not those indicated.
Try it this way:

SELECT DISTINCT(matricula.mat_id)
FROM matricula
INNER JOIN professor_turma ON prt_tur_id = mat_tur_id
INNER JOIN aluno_tutor ON at_pes_id = mat_aln_id
WHERE prt_tur_id = 92 AND prt_pes_id = 79