PostgreSQL query bringing more results than expected

Navigation
0

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 the tutor (prt_pes_id) and with the class (prt_tur_id)
  • aluno_tutor containing foreign key with teacher_turma (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 enrollments 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

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

The example in SQL Fiddle is here for ease of understanding in practice.

Can anyone help me?

    
asked by anonymous 10.04.2018 / 15:38

1 answer

1

From what I saw the error is in the second join , the selected fields are not the ones indicated. Try this: 

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
    
10.04.2018 / 15:51
Error in requests with aiohttp in asyncio Ternary Conditional Operator