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?

This question does not show any research effort. It is important to **do your homework**. Tell us what you found and ***why*** it didn't meet your needs. This demonstrates that you've taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer. [FAQ](http://stackoverflow.com/questions/how-to-ask). — Kermit, Mar 30 '13 at 18:50
Polish Prince: The reply to this question is not as obvious as you think. It is not basic SQL... — balteo, Mar 30 '13 at 19:01
@balteo: that is the reason why I added the relational-division tag. The question involves (a limited form of) RD. — wildplasser, Mar 30 '13 at 19:05

score 2 · Accepted Answer · 2013-03-30T20:21:13.940

2

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)

edited Mar 30 '13 at 20:21

answered Mar 30 '13 at 19:14

Hi: can you please explain why the `having count` is necessary? – balteo Mar 30 '13 at 19:29
1

@balteo: because otherwise you'd also get those that have only one of the two categories assigned. Why don't you simply try it without and see for yourself? (you can use the SQLFiddle examples) – Mar 30 '13 at 19:30
Umm, I am trying to port your solution to mysql as follows: [sqlfiddle](http://sqlfiddle.com/#!2/97362/1). Only advertisement one should be selected. I am not sure why this does not work. – balteo Mar 30 '13 at 19:43
Ooops. It seems MySQL does not like the intersect operator.... – balteo Mar 30 '13 at 19:45
1

@balteo: that's right, MySQL is lacking most of the modern SQL features. But this problem can also be solved using a co-related sub-query. See my edit – Mar 30 '13 at 20:22

wildplasser · Answer 2 · 2013-03-30T19:54:36.007

1

SELECT DISTINCT advertisementid 
FROM tft t1
WHERE t1.categoryid IN ('A','C')
AND EXISTS (
  SELECT * FROM tft t2
  WHERE t2.advertisementid = t1.advertisementid
  AND t2.categoryid IN ('A','C')
  AND t2.categoryid <> t1.categoryid
  );

edited Mar 30 '13 at 19:54

answered Mar 30 '13 at 19:14

wildplasser

43,142
8
66
109

Hi wildplasser! Ummm. I have used my schema on sqlfiddle as follows: [sqlfiddle](http://sqlfiddle.com/#!2/97362/3). Only advertisement 1 should be retrieved. Your sql query matches more than that... – balteo Mar 30 '13 at 19:49
Strange. It does work here. And reports only advertisementid=1. – wildplasser Mar 30 '13 at 19:59
Your fiddel tests for three categories, not two. My query works for two categories only. I cannot solve problems that haven't been shown. – wildplasser Mar 30 '13 at 20:02
Have you tried the following fiddle: [here:](http://sqlfiddle.com/#!2/97362/3)? Any idea why there is a difference between yours and mine? – balteo Mar 30 '13 at 20:02
1

Yes, because you changed the rules while playing the game. two != three, obviously. – wildplasser Mar 30 '13 at 20:04
Sorry. I had not realized this would change the query. – balteo Mar 30 '13 at 20:05
1

Yes of course it does. Mine handles the special case for ncategory=2. You would have to adapt the query by @a_horse_with_no_name too to satisfy your new requirements. – wildplasser Mar 30 '13 at 20:18
Thanks a lot to you too. – balteo Mar 30 '13 at 20:29

score 1 · Answer 3 · answered Mar 30 '13 at 20:47

It seems I came late, but here is my solution anyway:

SELECT advertisement
FROM   advertisement_childcare_types t1
LEFT JOIN (
       SELECT childcare_types ct
       FROM   table_childcare_types tct
       WHERE  childcare_types IN (0, 1, 3)
          ) AS mytypes
ON     t1.childcare_types = mytypes.ct
GROUP BY advertisement
HAVING SUM(IF(mytypes.ct IS NULL, -1, 1)) = 3;

You can test in your sqlfiddle using this modified version:

SELECT advertisement
FROM   advertisement_childcare_types t1
LEFT JOIN (SELECT 0 as ct UNION SELECT 1 UNION SELECT 3) AS mytypes
ON     t1.childcare_types = mytypes.ct
GROUP BY advertisement
HAVING SUM(IF(mytypes.ct IS NULL, -1, 1)) = 3;

Translating a set theory idea into SQL

3 Answers3