Select with two tables and one condition in each with MYSQL

Question

I have two unrelated tables. One is of reports, where it contains names of teachers and their respective subject, the other table is called login, where it contains credentials of each teacher, their privilege and also their respective subject.

The idea of the login table is that, with the access of the teacher, he can visualize all the teachers of the report table when their subject is equal to that of the registered teacher, and that also the privilege of the teachers is of teacher.

LOGIN TABLE

id
nome
materia
privilegio

REPORT TABLE

nome
materia

QUERY USED:

SELECT diga_relatorio.nome, diga_relatorio.materia, diga_login.materia 
FROM diga_relatorio, diga_login 
WHERE diga_relatorio.materia='{$_SESSION['digaPrivilegio']}' 
AND diga_login.privilegio='professor' 
GROUP BY diga_relatorio.nome 
ORDER BY diga_relatorio.nome

However, this way it is returned also the teachers with the position of coordinator, being wrong, since the hierarchy would be:

1. COORDINATOR (view all others)
2. SUPERVISOR (view only teachers of your subject)
3. TEACHER (see for himself)

Answer 1

When you want to relate one table to another through a field, you have to use one inner join and mention which field binds the tables, otherwise you make a outter join in which you unite everything with everything.

I removed the group by because I didn’t realize the usefulness in it in query.

Try:

SELECT diga_relatorio.nome, diga_relatorio.materia, diga_login.materia 
FROM diga_relatorio 
INNER JOIN diga_login 
ON diga_relatorio.materia = diga_login.materia 
WHERE diga_relatorio.materia='{$_SESSION['digaPrivilegio']}' 
AND diga_login.privilegio='professor' 
ORDER BY diga_relatorio.nome