Griff's answer works if you only have two entries for each
id1
. But there's nothing in your question to suggest such a limitation. :)
A correlated sub-query with a
Not Exists
is probably the clearest solution:
SELECT DISTINCT id1
FROM A As A1
WHERE Not Exists
(
SELECT 1
FROM A As A2
WHERE A2.id1 = A1.id1
And A2.status = 0
)
Alternatively, you could use
EXCEPT
:
SELECT id1 FROM A WHERE status = 1
EXCEPT
SELECT id1 FROM A WHERE status = 0
EXCEPT and INTERSECT (Transact-SQL) - SQL Server | Microsoft Learn[
^]