[SOLVED] Find single entries where there should be 2

Issue

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

Solution

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

Answered By – Stu

Answer Checked By – Terry (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.