SQL Get data with territory 62 and 69 but not in t

Question

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.

Answer 1

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
       )