Exclude All Rows For An Id If 1 Row Meets Condition

I am trying to select certain customers from a contacts table if they do not have a guardian listed. ClientId | ContactId | Guardian 123 | 1 | Y 123 | 2

Solution 1:

The reason I believe you're experiencing that is because you were connecting the subquery using contactid instead of clientid. I've also included distinct in the output:

selectdistinct c.ClientId
from Contacts c
wherenotexists (select1from Contacts c2
                  where c2.ClientId = c.ClientId
                  and c2.Guardian ='Y')  

Solution 2:

The subquery gets the ClientIds that do not have any Guardian = 'Y'. If you need the complete record use the outer query too. If you need only the ID then use just the subquery

select*from Contacts
where ClientId in
(
   select ClientId
   from Contacts
   groupby ClientId
   havingsum(casewhen Guardian ='Y'then1else0end) =0
)                

Solution 3:

select * 
from contacts 
where ClientId notin 
(select ClientId 
    from contacts 
    where Guardian = 'Y')