I would like to grab data that have territory 62 and 69 but that do not have territory 200 and 92 , I am using this query but that doesn't work
select rtp1.releaseId, rtp1.territoryId
from ReleaseTerritoryPrice rtp1
where rtp1.territoryId in (62,69)
and not exists (select releaseId
from ReleaseTerritoryPrice t2
where t2.territoryId in (200,92)
and rtp1.releaseId = t2.releaseId);
Any Help ?
Thanks.
This is an example of a set-within-sets query. I like to use aggregation with a having clause for this, because this is a very flexible approach:
select ReleaseId
from ReleaseTerritoryPrice
group by ReleaseId
having (sum(case when territoryId = 62 then 1 else 0 end) > 0 and
sum(case when territoryId = 69 then 1 else 0 end) > 0
) and
(sum(case when territoryId = 200 then 1 else 0 end) = 0 and
sum(case when territoryId = 92 then 1 else 0 end) = 0
)
Each condition in the having
clause is counting the number of rows with each of the territories. The first two are saying that 62
and 69
must be present (the counts are greater than 1). The last two are saying that 200
and 92
are not present (the counts are 0).
As an example, if you wanted to change this so that only one of 62
and 69
were needed but not the other two, the having
clause would be:
having (sum(case when territoryId = 62 then 1 else 0 end) > 0 or
sum(case when territoryId = 69 then 1 else 0 end) > 0
) and
(sum(case when territoryId = 200 then 1 else 0 end) = 0 and
sum(case when territoryId = 92 then 1 else 0 end) = 0
)