Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL select join where AND where

I have two tables in my database:

Products

  • id (int, primary key)
  • name (varchar)

ProductTags

  • product_id (int)
  • tag_id (int)

I would like to select products having all given tags. I tried:

SELECT
    *
FROM
    Products
JOIN ProductTags ON Products.id = ProductTags.product_id
WHERE
    ProductTags.tag_id IN (1, 2, 3)
GROUP BY
    Products.id

But it gives me products having any of given tags, instead of having all given tags. Writing WHERE tag_id = 1 AND tag_id = 2 is pointless, because no rows will be returned.

like image 801
Darrarski Avatar asked Feb 16 '11 14:02

Darrarski


1 Answers

This type of problem is known as relational division

SELECT Products.* 
FROM Products
JOIN ProductTags ON Products.id = ProductTags.product_id
WHERE ProductTags.tag_id IN (1,2,3)
GROUP BY Products.id /*<--This is OK in MySQL other RDBMSs 
                          would want the whole SELECT list*/

HAVING COUNT(DISTINCT ProductTags.tag_id) = 3 /*Assuming that there is a unique
                                              constraint on product_id,tag_id you 
                                              don't need the DISTINCT*/
like image 106
Martin Smith Avatar answered Sep 28 '22 22:09

Martin Smith