CODEWITHSUNDEEP
sqlsql-servert-sqlselect
jfe042
jfe042

Reputation: 45

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         | N
123      | 3         | N

456      | 4         | N
456      | 5         | N
456      | 6         | N

Desired output:

ClientId | ContactId | Guardian 
456      | 4         | N
456      | 5         | N
456      | 6         | N

So my goal is that Client 456 would show up in my query results, but not Client 123. I have written the following:

select * from Contacts
where Guardian <> (case when Guardian = 'Y' 
    then Guardian 
       else '' 
         end)

I also tried

select * from Contacts c
where not exists (select 1
                  from Contacts c2
                  where c2.ContactId = c.ContactId
                  and c.Guardian = 'Y')

But my results are just excluding the lines where Guardian = Y, and printing the lines where Guardian = N, even though if there is any line associated with a ClientId where Guardian = Y, that ClientId should not show up in the results. I've been looking up how to only select rows with certain values in them, but I'm not having any luck finding how to exclude a ClientId entirely if one of its rows matches.

I'd be really grateful for any suggestions!

Upvotes: 2

Views: 3127

Answers (3)

Nader Hisham
Nader Hisham

Reputation: 5414

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

Upvotes: 0

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17137

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:

select distinct c.ClientId
from Contacts c
where not exists (select 1
                  from Contacts c2
                  where c2.ClientId = c.ClientId
                  and c2.Guardian = 'Y')

Upvotes: 2

juergen d
juergen d

Reputation: 204746

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
   group by ClientId
   having sum(case when Guardian = 'Y' then 1 else 0 end) = 0
)

Upvotes: 1

Related Questions