CODEWITHSUNDEEP
sqlsql-servert-sqlcorrelated-subquery
Srinivas Adari
Srinivas Adari

Reputation: 105

Sql server Multiple conditions subquery

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.

Upvotes: 1

Views: 13062

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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.

Upvotes: 2

bummi
bummi

Reputation: 27377

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)
)

Upvotes: 3

Related Questions