如果 1 行满足条件，则排除 ID 的所有行 [英] 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

所需的输出:

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

所以我的目标是客户端 456 会出现在我的查询结果中，但 不会 客户端 123.我写了以下内容:

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)

我也试过

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

但我的结果只是排除了 Guardian = Y 的行，并打印了 Guardian = N 的行，即使有任何行与 Guardian = Y 的 ClientId 相关联，该 ClientId 也不应该出现在结果中.我一直在寻找如何只选择其中具有某些值的行，但是如果其中一行匹配，我没有找到如何完全排除 ClientId 的运气.

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!

推荐答案

子查询获取没有任何 Guardian = 'Y' 的 ClientId.如果您需要完整的记录，也可以使用外部查询.如果您只需要 ID，则只需使用子查询

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
)                

这篇关于如果 1 行满足条件，则排除 ID 的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！