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

Question

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 1

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!

Answer 2

SELECT MIN(ID), CommonID, MIN(Reject) as Reject
FROM   yourtable
GROUP BY CommonID
HAVING  MIN(ISNULL(Reject, ''))  = MAX(ISNULL(Reject, ''))
AND     MIN(ISNULL(Reject, '')) = 'Yes'

EDIT : as you have NULL value, will need to use ISNULL() on the column