Do not show element that has a certain value

Asked

Viewed 41 times

1

In Mysql I have a table called user where all website users are stored:

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

And a table called user_management, where are fields related to managing these users:

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

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

What I’m not able to do is give a select to users, and NOT show users that have CREDENCIAL_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 users, I even understand that this occurs because users have other credentials beyond 2, but how to do?

mysql

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)

2


You’ll 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 
);

2

This will require using a sub-query, in this way:

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 the 2 credential.

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

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

Can be read like this:

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

See working on Sqlfiddle

Browser other questions tagged mysql

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