問題描述
如果某些客戶沒有列出監護人，我正在嘗試從他們的聯系人表中選擇他們.
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 的所有行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！