Sql server Multiple conditions subquery

Question

I tried to use compound conditions in the subquery, but it didn't return the result expected. Can you look into the example below to see why the query doesn't work out ?

Table : 1
EntityID   StartDate    EndDate

121        2013-08-01   2013-08-31
122        2013-08-01   2013-08-31
123        2013-08-01   2013-08-31


Table : 2
EntityID     AttributeID    AttributeValue

121            41                 304
122            41                 304
123            41                 304
123            54                 307    

Now I'm trying to fetch based on AttributeID and AttribueValue from Table-2 and Stardate and enddate from table1 using following query. (Ex: The 41 and 304 and 54 and 307 got satisfied with 123 only I want fetch that 123 only one record)

SELECT pe.EntityID
FROM  table1 pe          
WHERE  pe.StartDate = '2013-08-01'
       AND pe.EndDate = '2013-08-31'
       AND pe.EntityID IN (SELECT peaiv.EntityID
                     FROM   table2 peaiv
                     WHERE  peaiv.AttributeID IN (41)
                            AND peaiv.[Value] IN (304)
                            AND peaiv.EntityID IN (SELECT peaiv.EntityID
                                                   FROM   
                                                          PT_EntityAttributesIntValues 
                                                          peaiv
                                                   WHERE  peaiv.AttributeID IN (54)
                                                          AND peaiv.[Value] IN (307))


EntitID
--------
121
122
123

The query returning the above result, but I'm expecting result only 123. Can any one try on this.

Answer 1

Declare @Table1 table(EntityID int,  StartDate datetime,    EndDate datetime)
Insert into @Table1
SELECT 121,'2013-08-01','2013-08-31'
UNION SELECT 122,'2013-08-01','2013-08-31'
UNION SELECT 123,'2013-08-01','2013-08-31'

Declare @Table2 Table (EntityID int, AttributeID int , AttributeValue int)
Insert into @Table2
SELECT 121,41,304
UNION SELECT 122,41,304
UNION SELECT 123,41,304
UNION SELECT 123,54,307

SELECT EntityID FROM @Table1 pe
WHERE
pe.StartDate = '2013-08-01' AND pe.EndDate = '2013-08-31'
AND EntityID in
(SELECT EntityID from @Table2
WHERE (AttributeID=41 and AttributeValue=304)
)
AND EntityID in
(SELECT EntityID from @Table2
WHERE (AttributeID=54 and AttributeValue=307)
)

Answer 2

I would approach this as a "set-within-sets" query, and then join back to table 1 to fill in those details:

select t1.*
from table1 t1 join
     (select EntityId
      from table2 t2
      group by EntityId
      having sum(case when AttributeId = 41 and AttributeValue = 304 then 1 else 0 end) > 0 and
             sum(case when AttributeId = 54 and AttributeValue = 307 then 1 else 0 end) > 0
     ) t2
     on t1.EntityId = t2.EntityId;

Each condition in the having clause is testing for one pair of values in the set of rows that match and entity. This makes it easy to add additional conditions.