Check in a table if a user is not registered

Question

Check in a table if a user is not registered

Navigation

#1 by (4 votes)
#2 by (2 votes)

2

Colleagues,

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

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 list all users and not only room 93

sql sql-select

asked by anonymous 30.06.2016 / 23:22

2 answers

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

30.06.2016 / 23:28

HEAD is detached in repository Why can not I capture exceptions triggered by async void?

score 4 · Accepted Answer

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 that context.