Filtering from join-table

Question

I'm having some trouble with a tricky SQL-query.

In my MySQL database there is the tables topics, tags and tags_topics to join them. I want to fetch topics that share the same specified tags. For example, let's say i have 3 tags with ids 1, 2 and 3, i want to fetch all topics that have tag 1, 2 and 3 associated to them. The topics can have other tags, but must have all of the specified tags.

Help me think plz xD

EDIT: Found a solution using GROUP BY in this question: Fetching only rows that match all entries in a joined table (SQL) If anyone have a more elegant solution, please post :)

Bill Karwin · Accepted Answer · 2009-11-23T20:51:41.187

5

JOIN solution:

SELECT t.*
FROM topics t
 JOIN tags_topics t1 ON (t.id = t1.topicId AND t1.tagId = 1)
 JOIN tags_topics t2 ON (t.id = t2.topicId AND t2.tagId = 2)
 JOIN tags_topics t3 ON (t.id = t3.topicId AND t3.tagId = 3)

GROUP BY solution:

Note that you need to list all t.* columns in the GROUP BY clause, unless you use MySQL or SQLite.

SELECT t.*
FROM topics t JOIN tags_topics tt 
  ON (t.id = tt.topicId AND tt.tagId IN (1,2,3))
GROUP BY t.id, ...
HAVING COUNT(*) = 3;

Subquery solution:

SELECT t.*
FROM topics t
WHERE t.id = ANY (SELECT topicId FROM tags_topics tt WHERE tt.tagId = 1)
  AND t.id = ANY (SELECT topicId FROM tags_topics tt WHERE tt.tagId = 2)
  AND t.id = ANY (SELECT topicId FROM tags_topics tt WHERE tt.tagId = 3);

Modified GROUP BY solution:

Simplifies GROUP BY clause by isolating search in a subquery.

SELECT t.*
FROM topics t
WHERE t.id IN (
  SELECT tt.topicId FROM tags_topics tt 
  WHERE tt.tagId IN (1,2,3))
  GROUP BY tt.id HAVING COUNT(*) = 3
);

edited Nov 23 '09 at 20:51

answered Mar 15 '09 at 19:36

Bill Karwin

538,548
86
673
828

COUNT(*) should be > 2, because topics can also have other tags. Thanks for all suggestions though! Edit that, and i'll set it as the right answer ;) – finpingvin Mar 15 '09 at 19:48
@finpingvin - the other tags are not considered in the statement. The COUNT(*) = 3 will be correct, so long as there are no duplicates in the junction table – Russ Cam Mar 15 '09 at 20:01
Yes, I'm assuming tags_topics has a primary key over {topicId, tagId}. Btw, I edited the fourth solution to avoid a join inside the subquery, after reading @Russ Cam's solution. – Bill Karwin Mar 15 '09 at 20:10

Russ Cam · Answer 2 · 2009-03-15T19:35:10.397

3

SELECT 
    topic_id
FROM
    tags_topics
WHERE
    tag_id IN (1,2,3)
GROUP BY
    topic_id
HAVING
    COUNT(*) > 2  /* or use COUNT(*) = 3 if you know that there cannot be duplicates in the junction table */

edited Mar 15 '09 at 19:35

answered Mar 15 '09 at 19:15

Russ Cam

124,184
33
204
266

1

+1 for avoiding the join, by using only the intersection table. This provides the relevant topic_id values, but not the full topic entity. – Bill Karwin Mar 15 '09 at 20:11

Filtering from join-table

2 Answers2

Linked