Translating a set theory idea into SQL

Question

Say I have a the following db table:

╔═════════════════╦════════════╗
║ ADVERTISEMENTID ║ CATEGORYID ║
╠═════════════════╬════════════╣
║               1 ║ A          ║
║               1 ║ C          ║
║               2 ║ A          ║
║               2 ║ B          ║
║               3 ║ A          ║
╚═════════════════╩════════════╝

Given a list of categories passed as a parameter, say A, C I want to find only those advertisements that belong only to those two categories, in the above case only advertisement 1 would match.

Can anyone please help me translate this into SQL?

Answer 1

select advertismentid
from the_table
where categoryid in ('A', 'C')
group by  advertismentid
having count(*) = 2;

SQLFiddle: http://sqlfiddle.com/#!12/b94d6/1

This assumes that the same categoryid cannot be assigned more than once to the same advertismentid. It will also include advertisments that have A,C and other categories.

If you want those advertisments that have exactly categories A and C you need to exclude those that have more than that:

select advertismentid
from the_table
where categoryid in ('A', 'C')
group by  advertismentid
having count(*) = 2;
intersect
select advertismentid
from the_table
group by advertismentid
having count(*) = 2;

SQLFiddle: http://sqlfiddle.com/#!12/8901c/4
The SQLFiddle also has another solution using except instead of intersect

If your DBMS is limited and you cannot use except or intersect, you can use this alternative:

select t1.advertismentid
from the_table t1
where t1.categoryid in ('A', 'C')
group by t1.advertismentid
having count(*) = 2
and count(*) = (select count(*) 
                from the_table t2
                where t2.advertismentid = t1.advertismentid)