Mysql query using JOIN

Question

Hello! I have the following database:

I would like to make a query 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 performing my queries it displays the teacher x related tbm to the student w msm not stating the relation in the 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;

Please someone could help me with this?

Answer 1

Your mistake lies in this passage:

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

Ended up mixing two ways to make 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 using more than one table you should 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 to assemble your select and put 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 student W even if it’s not the teacher with id = 2. You have to interpret as follows, you have a student table, a teacher table and a link table between the two, your select should be made from the student table and the teacher table for the link 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 don’t know if the student table fields are correct, but the logic is this connecting your two tables in the link table.

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

Answer 2

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 the 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'