PLSQL - Query between tables

Question

Hello, I would like suggestions on how to perform a query between two tables (Person and Person Permissions), where we have the table Person (person, name), the table Permissions(Permission_id, Description) and the table Permission_person(Personal permission_id, person, permission_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 that when executing the query, be returned users who do not have the permission of collaborator (3).

Thanks in advance.

Updating

I need the query to return the person’s name and id (person, 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;

Answer 1

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

In this case, use the operator (NOT) EXIST

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
)