Select distinct rows with same value in another column-CodePudding

I have a table like this:

 -------- ---------- 
|PersonID|IsDomestic|
 -------- ---------- 
|1       |1         |
 -------- ---------- 
|1       |0         |
 -------- ---------- 
|2       |1         |
 -------- ---------- 
|2       |1         |
 -------- ---------- 
|2       |1         |
 -------- ---------- 
|1       |1         |
 -------- ---------- 
|3       |0         |
 -------- ---------- 
|4       |1         |
 -------- ----------

If the same PersonId have at least one 0 value in IsDomestic column then shouldn't return this PersonId but if PersonId have only 1 values then should return this PersonId but only once. This is result from the table:

 -------- 
|PersonID|
 -------- 
|2       |
 -------- 
|4       |
 --------

CodePudding user response：

What you need is a HAVING with a conditional aggregate:

SELECT PersonID
FROM dbo.YourTable
GROUP BY PersonID
HAVING COUNT(CASE IsDomestic WHEN 0 THEN 1 END) = 0;