MySQL query using JOIN

0

Hello! I have the following database:

I would like to make an appointment where I can search for example the teacher x that is related to the student y and z but not the student w. However when executing my queries it displays the teacher x related tbm to the student w msm not listing the relation in the student_has_professor table.

SELECT nomealuno, nomeprofessor FROM  aluno, professor as A
INNER JOIN aluno_has_professor as B ON (B.professor_idprofessor = A.idprofessor)
WHERE idprofessor = 2;

Why could anyone help me with this?

    
asked by anonymous 22.11.2018 / 15:01

2 answers

1

Try this query:

SELECT 
    p.nomeprofessor,a.nomealuno 
from professor p
JOIN aluno_has_professor ap on p.idprofessor = ap.professor_idprofessor 
JOIN aluno a on a.idaluno = ap.aluno_idaluno
WHERE
p.nomeprofessor='Walmir'

or by id

SELECT 
    p.nomeprofessor,a.nomealuno 
from professor p
JOIN aluno_has_professor ap on p.idprofessor = ap.professor_idprofessor 
JOIN aluno a on a.idaluno = ap.aluno_idaluno
WHERE
p.idprofessor ='2'
    
22.11.2018 / 16:59
3

Your error is in this section:

FROM  aluno, professor as A
INNER JOIN aluno_has_professor as B ON (B.professor_idprofessor = A.idprofessor)

You ended up mixing two ways to select, using INNER JOIN or not. You perform select in two ways:

SELECT * FROM professor AS A 
INNER JOIN aluno_has_professor as B ON 
(B.professor_idprofessor = A.idprofessor)

Or

SELECT * FROM professor AS A, aluno_has_professor as B 
WHERE B.professor_idprofessor = A.idprofessor

Whenever you use more than one table you must make the relationship between them and the difference in this two select's is that using the INNER JOIN the relationship is done after the ON and NOT USING THE INNER JOIN the relationship is done after the WHERE.

In your code you ended up mixing the two ways when assembling your select and when putting FROM student, teacher and do not put any relationship between the two tables, you end up bringing everything from the two tables. That's why you bring the W student even though you are not a teacher with id = 2. You have to interpret as follows, you have a student table, a teacher table and a binding table between the two, your select must be done from the student table and from the teacher table to the connection table.

Select * from professor as A 
INNER JOIN aluno_has_professor as B ON (B.professor_idprofessor = A.idprofessor)
INNER JOIN aluno as C ON (B.aluno_idaluno = C.idaluno)
WHERE idprofessor = 2;

I do not know if the student table fields are correct, but the logic is this linking your two tables in the binding table.

With this in choosing the teacher of ID = 2, the link table will identify only those students who are of teacher ID = 2 in the student table.

    
22.11.2018 / 17:15