Markorie Markorie - 2 years ago 56
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 Source
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!


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!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download