如果 1 行满足条件，则排除 ID 的所有行
如果某些客户没有列出监护人，我正在尝试从他们的联系人表中选择他们.
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
)
相关文章