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?

Please elaborate this "term_id = 1 AND 2 AND 3 must exists"

Manashvi Birla
– Manashvi Birla

2015-06-09 05:49:27 +00:00
Commented Jun 9, 2015 at 5:49 — Manashvi Birla
– Manashvi Birla, Commented Jun 9, 2015 at 5:49

Mureinik · Accepted Answer · 2015-06-09 05:52:04Z

2

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)

answered Jun 9, 2015 at 5:52

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Raging Bull · Accepted Answer · 2015-06-09 05:56:44Z

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.

edited Jun 9, 2015 at 5:56

answered Jun 9, 2015 at 5:51

Raging Bull

18.8k13 gold badges52 silver badges56 bronze badges

1 Comment

hvs Over a year ago

yes, your query does work! How do I join this to the creatives table?

Collectives™ on Stack Overflow

mysql inner join query with multiple boolean conditions

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related