Do not show element that has a certain value

Question

Do not show element that has a certain value

Navigation

#1 by (3 votes)
#2 by (2 votes)
#3 by (2 votes)

1

In MySQL I have a table named user where all users of the site are stored:

ID | NOME
 1 | bla
 2 | ble
 3 | bli
 4 | blo

And a table called user_administration , where there are fields related to the management of these users:

USUARIO_ID | CREDENCIAL_ID
    1      |     1
    2      |     1
    2      |     2
    3      |     1
    4      |     1
    4      |     2

(the tables are much more complex, simplified to focus on my doubt)

What I am not able to do is to give a select on users, and DO NOT show users who have the CREDENTIAL_ID = 2, my code so far:

SELECT usuario.id AS usuarioId FROM usuario
LEFT JOIN usuario_gerenciamento ON usuario.id = usuario_gerenciamento.usuario_id
WHERE usuario_gerenciamento.credencial_id <> 2

But it shows all the users, I even understand that this happens because the users have other credentials besides the 2, but how to do?

mysql

asked by anonymous 09.08.2018 / 20:09

3 answers

3

SELECT usuario.id AS usuarioId
FROM usuario
LEFT JOIN usuario_gerenciamento ON usuario.id = usuario_gerenciamento.usuario_id
WHERE usuario.id NOT IN 
(SELECT usuario_id FROM usuario_gerenciamento WHERE credencial_id = 2)

09.08.2018 / 20:24

2

For this you will need to use a sub-query, like this:

SELECT usuario.id 
FROM usuario 
WHERE usuario.id in (
    SELECT usuario_id FROM usuario_gerenciamento
    WHERE usuario_gerenciamento.credencial_id = 2 
);

The sub-query will only bring users with credential 2.

To bring all those who do not have credential 2, you can use NOT IN as the query criteria:

SELECT usuario.id 
FROM usuario 
WHERE usuario.id not in (
    SELECT usuario_id FROM usuario_gerenciamento
    WHERE usuario_gerenciamento.credencial_id = 2 
);

It can be read like this:

Bring users who are NOT in the user group with the EQUAL credential to 2.

See working in SQLFiddle

09.08.2018 / 20:22

Search for an array key and return value Show only option marked in select in JS

score 2 · Accepted Answer

You will have to do with sub-select :

SELECT usuario.id AS usuarioId
FROM usuario
LEFT JOIN usuario_gerenciamento ON usuario.id = usuario_gerenciamento.usuario_id
WHERE NOT EXISTS (
    SELECT * FROM usuario_gerenciamento
    WHERE usuario_gerenciamento.usuario_id = usuario.id
        AND usuario_gerenciamento.credencial_id = 2 
);