Select with two tables and one condition in each with MYSQL [closed]

Question

Select with two tables and one condition in each with MYSQL [closed]

Navigation

#1 by (3 votes)

3

I have two tables that do not relate. One is of reports, where it contains the name of teachers and their respective subject, the other table is called login, which contains credentials of each teacher, their privilege and also their respective subject.

The idea of the login table is that, with teacher access, it can view all the teachers in the report table when the subject matter is the same as that of the logged in teacher, and that the teacher privilege is also a teacher's

TABLE LOGIN

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 the teachers with the position of coordinator are also returned, being wrong, since the hierarchy would be:

COORDINATOR (view all others)

SUPERVISOR (only view the teachers of your subject)

TEACHER (visualize himself)

mysql sql

asked by anonymous 26.09.2016 / 14:56

1 answer

Creating dateTime field with laravel collective Cryptographic test diverging from class execution

score 3 · Answer 1

When you want to relate one table to another through a field, you have to use a inner join and mention the field that joins the tables, otherwise you make a outter join where you join everything with everything. >

I removed group by because I did not see the utility in it in the 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