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 |
+--------+
>Solution :
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;