Using relational division in mysql on a simple associative table

Question

We have an associative table that connects threads with tags. We are trying to figure out a query that would give us any thread ID's that are tied to a combination of tag ID's. I created an sqlfiddle at:

http://sqlfiddle.com/#!9/c8ebe/3

So the table is structured like this:

 CREATE TABLE `threads_tags` (
  `ID_threads` bigint(20),
  `ID_tags` bigint(20)
);

Some sample data may look like this:

ID_threads   ID_tags
  2             12
  4             12
  9             10
  2             21
  3             2
  2             1
  5             1
  5             21

In our example, we would have tag ID's 12 and 1 and we need a query that would return 2 for the ID_threads because that is the only ID_threads that has both ID_tags = 12 and ID_tags = 1. There is no limit to the number of tags that we may need to match against. I think we need to be using relational division and I tried using this query:

SELECT
    ID_threads
FROM
    threads_tags tt1
WHERE
    ID_tags IN (1,12)
GROUP BY
    ID_threads
HAVING
    count(*) = (
        SELECT
            count(*)
        FROM
            threads_tags tt2
        WHERE
            ID_threads = tt1.ID_threads
    )

It doesn't seem to be working though. I thought about using something like the solution at Relational division in mysql without aggregrate functions? but in that example, I am not sure where to put the list of ID_tags that I want to input into the query. I am really new at relational division type of queries so any help you can give me here would be much appreciated.

Answer 1

This should be what you're looking for:

SELECT ID_threads FROM threads_tags
    WHERE ID_tags IN (1,12)
 GROUP BY ID_threads
 HAVING COUNT(DISTINCT ID_tags) = 2;

The number 2 after HAVING COUNT... is the number of values in your list (in this case it's 2 - 1 and 12).