How to select records where all of their statuses are zero?

Question

Suppose I have following tables Person table and personStatus table.

declare @Persons table
(PersonId int)

insert into @Persons select 10   
insert into @Persons select 11    

declare @PersonStatus table
(id int,statuss int)

insert into @PersonStatus (id,statuss) values(10,0)
insert into @PersonStatus (id,statuss) values(10,0)
insert into @PersonStatus (id,statuss) values(11,1)
insert into @PersonStatus (id,statuss) values(10,0)
insert into @PersonStatus (id,statuss) values(11,0)

Now I want to find person IDs that all of their statuses are zero result is just ---> 10

How to do it?

Answer 1

SELECT  id
FROM    @PersonStatus
GROUP   BY ID
HAVING  COUNT(DISTINCT statuss) = 1 AND
        MAX(statuss) = 0

• SQLFiddle Demo

OR

SELECT  id
FROM    @PersonStatus
GROUP   BY ID
HAVING  MAX(statuss) = MIN(statuss) AND
        MAX(statuss) = 0

• SQLFiddle Demo