Get Distinct Values With Consistent Value

I have a table with some IDs and a flag of whether or not the person should be deleted. There are 2 sets of IDs in the table, 1 primary ID, and then a secondary ID as well. A single primary ID can be in the table multiple times if it has multiple secondary IDs.

Take this set of data for example:

primary_id       secondary_id      delete_flag
   1                 ajdkl              1
   1                 jnami              0
   2                 janda              0
   3                 wwqia              1
   4                 lamse              1
   4                 pppqw              1
   4                 aneqw              0
   5                 gaabs              1
   5                 iikam              1

in that example I would be looking to have 3 and 5 returned since they are the only 2 where all instances of their primary ID have a deleted_flag = 1.

What I am running in to in this query:

select primary_id from table where delete_flag = 1 group by primary_id 

is that it is returning all instances where there at least a single 1 present, I want only the ones where every instance has a 1 present.

>Solution :

You need to use GROUP BY and HAVING clauses, where the primary_ids whose total number of records is equal to the total number of deleted flag records should be selected.

select primary_id 
from mytable 
group by primary_id 
having count(delete_flag) = count(case when delete_flag = 1 then 1 end)

Another option is to use MIN() and MAX(), where the deleted_flag’s min and max should both equal 1.

select primary_id 
from mytable 
group by primary_id 
having min(delete_flag) = 1 and max(delete_flag) = 1

Demo here