CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2005sql-server-2000
Raymond Morphy
Raymond Morphy

Reputation: 2526

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

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?

Upvotes: 0

Views: 62

Answers (3)

John Woo
John Woo

Reputation: 263843

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

OR

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

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280490

Since I assume the @Persons table has more than just a PersonId column and you might want other columns from there, I think @Persons needs to be part of the query.

SELECT p.PersonId --, other columns from p
 FROM @Persons AS p
 WHERE EXISTS (SELECT id FROM @PersonStatus
   WHERE id = p.PersonId
   GROUP BY id HAVING MAX(statuss) = 0);

Upvotes: 1

aweis
aweis

Reputation: 5606

select distinct p.id from @PersonStatus as p
where Statuss = 0
and not exists (select null from @Personstatus as t
                where p.id = t.id and t.statuss <> 0)

Upvotes: 0

Related Questions