I am looking to find all the single entries in a table where there should only be double entries.
Eg.
Unique_Key | ID | State_Sequence_ID | Localisation_Format_ID | File_Name |
---|---|---|---|---|
6644106 | 1315865 | 100 | 1 | 2064430-DNK.pac |
6644107 | 1315865 | 190 | 2 | 2064430.chk [DNK] |
I am looking to find all instances where the 2nd record does not exist.
The ID for each record will always be the same (although I do not know what that ID will be specifically) and the Localisation Format ID will always be 1 and 2. I am looking to find all entries where Localisation Format ID 2 does not exist.
SELECT *
WHERE ID has Localisation_Format_ID = 1
but does not have Localisation_Format_ID = 2
CodePudding user response:
This is a simple not exists criteria:
select *
from t
where not exists (
select * from t t2 where t2.Id = t.Id and t2.Localisation_Format_ID = 2
);