Search for missing records within the same table

Asked

Viewed 43 times

1

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'.

sql database firebird

2 answers

3

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)

1


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')

Browser other questions tagged sql database firebird

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