Search for missing records within the same table

Question

I have a table containing user information. it contains a field called competence. And when I want to pull information from some user I pull competence so:

SELECT * FROM registro WHERE competencia = '01/2019'

Here’s what I want to do: Find all the information that exists in the '01/2019' competence but do not exist in the '02/2019'.

Answer 1

You can tie it up primary key and use the operator NOT IN

Example:

SELECT r.Id FROM registro r
  WHERE 
    competencia = '01/2019' 
    AND r.id NOT IN (SELECT Id FROM registro WHERE competencia = '02/2019' and Id = r.id)

Answer 2

Just use a subselect to verify the existence:

SELECT * FROM registro WHERE competencia = '01/2019' AND NOT EXISTS (SELECT * FROM registro WHERE competencia = '02/2019')

The above command has an error.

You need to verify the existence or not for the same user and therefore need to correlate the queries:

SELECT * FROM registro r1 WHERE competencia = '01/2019' AND NOT EXISTS (SELECT * FROM registro r2 WHERE r1.usuario = r2.usuario AND competencia = '02/2019')