Get data that is not in the [duplicate] table

Navigation
0

In Mysql I have a login table, which shows who logged in to the system:

id | cod_user

After the website is ready and working, the client has passed me a list of cod_usuario that CAN log into the system, so I created a table can and did this verification every time someone logs in, who is not can can not log in without problems.

Now the customer wants to know who has already logged on to the system and who was not in the table can , how to do this?

It would be something like: 

SELECT cod FROM login
LEFT JOIN pode ON pode.cod_usuario = login.cod_usuario

But I do not know how to get the ones that are in the login table and are NOT in the can table

    
asked by anonymous 13.04.2018 / 16:10

3 answers

2

Verify that the return of LEFT JOIN is null: 

SELECT cod FROM login
LEFT JOIN pode ON pode.cod_usuario = login.cod_usuario
WHERE pode.cod_usuario IS NULL;

If it is NULL it does not exist in the pode table.

    
13.04.2018 / 16:12
1

1 - Using a subquery - has the advantage of being more obvious to the casual reader 

SELECT cod FROM login WHERE cod_usuario NOT IN (SELECT cod_usuario FROM pode);

2-Usingleftjoin-canrunfasterthanasubqueryandworksonallversionsofMySQL.

SELECTlogin.codFROMloginLEFTJOINpodeON(login.cod_usuario=pode.cod_usuario)WHEREpode.cod_usuarioISNULL;

    
13.04.2018 / 17:21
0

Another possibility: 

SELECT cod 
  FROM login
 WHERE NOT EXISTS (SELECT 1 
                     FROM pode 
                    WHERE pode.cod_usuario = login.cod_usuario)
    
13.04.2018 / 16:39
Browse table with N lines with FOR and find specific values How to create a data frame from a database based on the difference of two dates in a column of another categorical variable in software R