PLSQL - Query between tables

Navigation
-1
Hello, I would like suggestions on how I can perform a query between two tables (Person_Personal and Person), where we have the Person (id_id, name) table, the Permissions table (id_add, description) and the Person_Permission table (person_id permission, person_id , permission_id). 

Permissão = {{1,'Administrativo'},{2,'Usuário'},{3,'Colaborador'}}

Pessoa = {{1,'João'},{2,'Mario'},{3,'Maria'}}

Pessoa_Permissão = {{1,1,1},{2,1,3},{3,1,2},{4,2,2},{5,3,3}}

I need to return the users who do not have the employee permission (3) when executing the query.

Thank you in advance.

Update

I need the query to return the name and id of the person (id_id, name). Below is the current status of my attempts. 

select p.pessoa_id, p.nome
from pessoa p
join pessoa_permissão pes on pes.pessoa_id = p.pessoa_id
where pes.permissao_id <> 3;
    
asked by anonymous 30.11.2018 / 12:24

1 answer

2

You were on the way, but your query brought all the permissions of the person, less than 3, which does not mean that the permission does not exist ... And if there was, you were removing it from the query.

In this case, use the EXIST (NOT) operator 

SELECT
  p.pessoa_id,
  p.nome
FROM pessoa p
WHERE NOT EXISTS (
  SELECT 1
  FROM pessoa_permissao pp
  WHERE pp.pessoa_id    = p.pessoa_id
    AND pp.permissao_id = 3
)
    
30.11.2018 / 12:42
UnitTest Django Views: I can not login to the system by unittest [closed] Transform one-dimensional multidimensional array into VUE