Selecting rows in SQL only where *all* values do not meet a certain criteria

Question

Given a really simple table in MS SQL like this:

User ID | Status
----------------
1         False
2         True
2         False
3         False
3         False

I'm having trouble wrapping my head around how I would select only users that don't have any rows with a Status assigned to True.

The result would return User IDs 1 and 3.

I have a feeling it's requires more than a straight forward WHERE selector, and have been experimenting with GROUP BY and COUNT without success.

Answer 1

You can use a GROUP BY with a HAVING clause to get the result. In your HAVING you can use a CASE expression with an aggregate to filter out any rows that have a Status = 'True':

select [user id]
from table1
group by [user id]
having sum(case when status = 'true' then 1 else 0 end) < 1;

See SQL Fiddle with Demo