Check in a table if a user is not registered

Asked

Viewed 48 times

2

Colleagues,

I have a table called salas_usuarios, this table contains all users registered in the rooms and another table called users, where I store users. But I would like to check which users are not registered in a certain room. The query I am using is the following:

SELECT * 
FROM usuarios USU
INNER JOIN sala_usuarios SAL ON USU.id_usuario != SALA.codigo_usuario_fk
INNER JOIN predio PRE ON PRE.codigo_predio = SAL.codigo_predio_fk
WHERE SALA.codigo_sala =93
GROUP BY USU.id_usuario

Only it lists all users and not only room 93

sql select-sql

  • Just don’t forget to use the ALIAS correctly, you defined as SAL but are using SALA. :)

2 answers

4


One way is this:

SELECT * 
FROM usuarios USU
LEFT JOIN sala_usuarios SALA ON USU.id_usuario = SALA.codigo_usuario_fk
WHERE SALA.codigo_sala = 93
and USU.id_usuario is null

I removed the Join with predio, because it is irrelevant to this context.

2

It would be something like:

SELECT distinct * 
FROM usuarios USU
INNER JOIN sala_usuarios SALA ON USU.id_usuario = SALA.codigo_usuario_fk
INNER JOIN predio PRE ON PRE.codigo_predio = SAL.codigo_predio_fk
WHERE USU.id_usuario not in (select distinct codigo_usuario_fk from sala_usuarios where codigo_sala = 93)
GROUP BY USU.id_usuario

Browser other questions tagged sql select-sql

You are not signed in. Login or sign up in order to post.