3

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    
insert into @Persons select 12   
insert into @Persons select 13  
insert into @Persons select 14    

declare @PersonStatus table
(id int,statuss int)
insert into @PersonStatus (id,statuss) values(14,4)
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,1)
insert into @PersonStatus (id,statuss) values(11,2)
insert into @PersonStatus (id,statuss) values(12,0)
insert into @PersonStatus (id,statuss) values(12,1)
insert into @PersonStatus (id,statuss) values(12,2)
insert into @PersonStatus (id,statuss) values(13,1)
insert into @PersonStatus (id,statuss) values(13,3)

Now I want to find person IDs that of their statuses are just in this set {0,1,2} result is ---> 10,11,12

How to do it?

John Woo
  • 258,903
  • 69
  • 498
  • 492
Raymond Morphy
  • 2,464
  • 9
  • 51
  • 93
  • If you ever plan on your "limiting" criteria being dynamic (or a parameter), check out the question I asked awhile back: http://stackoverflow.com/questions/7492699/how-can-i-structure-a-query-to-give-me-only-the-rows-that-match-all-values-in-a. It is the same as yours except my input was a CSV string of limit sets. – Gibron Mar 28 '13 at 07:28

2 Answers2

3
SELECT  ID
FROM    PersonStatus
GROUP   BY ID
HAVING  SUM(CASE WHEN statuss IN (0, 1, 2) THEN 1 ELSE 0 END) >= 1 AND
        SUM(CASE WHEN statuss NOT IN (0, 1, 2) THEN 1 ELSE 0 END) = 0
John Woo
  • 258,903
  • 69
  • 498
  • 492
2

Try this

SELECT DISTINCT ID
FROM @PersonStatus PT
WHERE NOT EXISTS (SELECT * FROM @PersonStatus WHERE statuss not in (0,1,2) AND id=PT.id)
Igor Borisenko
  • 3,806
  • 3
  • 34
  • 49