Checking if Field is Duplicate

Question

I have a Students table like this:

Name	Duplicate
John	no
Stacy	no
Kate	yes
John	yes
Stacy	yes
Kate	yes

It is supposed to be the first record is not flagged as a duplicate but if I receive a repeat record that is flagged as a duplicate. I am trying to write a query that checks if there are any distinct names that are all flagged as duplicates. In this example table, all Kate records are flagged as duplicate so the query should only return Kate.

I have tried:

select distinct Name from Students where duplicate='yes'

but this returns John, Kate, and Stacy

Answer 1

You can try doing it direct, as far as I can see, you want

1. All distinct Name such that
2. They are from Duplicate = 'yes' records
3. All such records must be Duplicate = 'yes' records; i.e. records Duplicate = 'yes' should not exist

select distinct s.Name 
  from s.Students
 where s.Duplicate = 'yes'
   and not exists ( 
         select 1
           from Students x
          where x.Duplicate = 'no'
            and x.Name = s.Name
       )

Answer 2

Do a GROUP BY, use HAVING to return names with only 'yes' rows.

select name
from students
group by name
having min(duplicate) = 'yes'

   and count(*) > 1   -- add this if student name must occur more than once