mysql inner join query with multiple boolean conditions

Question

I have following two tables

 Table 1: creatives
 creative_id       creative_name
      1                xyz
      2                pqr
      3                abc

 Table 2: term_relationships
 creative_id       term_id
      1                1
      1                2
      1                3
      2                1
      2                2
      2                4
      3                1
      3                2
      3                3

I want to join the above 2 tables to get query output containing creatives for which term_id = 1 AND 2 AND 3 must exists. For the above example, only creatives 1 (aka xyz) and 3 (aka abc) must be present in the query output since creative 2 does not satisfy the above condition.

So

SELECT * 
FROM term_id INNER JOIN 
     creatives ON term_id.creative_id = creatives.creative_id 
WHERE ????

What should the where clause look like?

Answer 1

One way to do this is to count the number of matching terms and check it sums up to the number of matches you want:

SELECT *
FROM   creatives
WHERE  creative_id IN (SELECT   creative_id
                       FROM     term_relationship
                       WHERE    term_id IN (1, 2, 3)
                       GROUP BY creative_id
                       HAVING   COUNT(*) = 3)

Answer 2

You can do this:

SELECT t.creative_id,t.term_id,c.creative_name
FROM term_relationships t INNER JOIN
     creatives c ON c.creative_id=t.creative_id
GROUP BY t.creative_id
HAVING SUM(CASE WHEN t.term_id IN (1,2,3) THEN 1 ELSE 0 END)=3

Result:

creative_id term_id creative_name
---------------------------------
1           1        xyz
3           1        abc   

Sample result in SQL Fiddle.