Markorie Markorie - 6 months ago 7
SQL Question

Select distinct group of records only when all of the records' certain column is of a certain value

I have 8 records below:

ID | Common ID | Reject
-------------------------
AB-1 | AB | NULL
AB-2 | AB | YES
AB-3 | AB | NULL
BB-1 | BB | YES
BB-2 | BB | YES
BB-3 | BB | YES
CB-1 | CB | YES
CB-2 | CB | YES
DB-1 | DB | NULL


My expected result is:

ID | Common ID | Reject
-------------------------
BB-1 | BB | YES
CB-1 | CB | YES


I only want to obtain distinct records when the reject column is yes for all of the records with the same Common ID.

Answer
select min(ID), [Common ID], max(Reject)
from tablename
group by [Common ID]
having count(*) = count(case when Reject = 'YES' then 1 end)

If a [Common ID] has the same number of rows as the number of YES, then return it!

The HAVING clause's count(*) returns the total number of rows for a [Common ID]. The case expression returns 1 if Reject = Yes, otherwise null. The right side count returns the number of rows where the case returns a non-null value (i.e. when Reject is yes!) When the same number of rows, HAVING is true!

Edit:

In this specific case, when the Reject column's values seem to be either YES or NULL, the HAVING can be simplified as:

having count(*) = count(Reject)

However, if other values (like NO) later will be found in the column, this won't work. So I recommend the original HAVING clause!

Comments