MYSQL - Get rows in all the selected categories

I'm trying to write query to a MYSQL database that queries for products that have a 开发者_运维知识库relationship with all selected categories

Products table

product_id | product_name
---------------------------------
    1      | product name one
    2      | product name two
    3      | product three

Category table

category_id | category_name
    1       | category one
    2       | category two
    3       | category three

Category_relationship table

product_id | category_id
--------------------------
    1      |    1
    1      |    2
    1      |    3
    2      |    1
    2      |    2
    3      |    3

So for example:

• category ID's 1 and 3 are selected
• Only product_id to be returned would be '1'

Using 2 JOINs

SELECT
    p.*
FROM products p
  JOIN category_relationship r1 
    ON r1.product_id = p.product_id 
  JOIN category_relationship r2
    ON r2.product_id = p.product_id 
WHERE r1.category_id = 1
  AND r2.category_id = 3

Using GROUP BY

SELECT
    p.*
FROM products p
  JOIN category_relationship r 
    ON r.product_id = p.product_id 
WHERE r.category_id IN (1,3)
GROUP BY p.product_id 
HAVING COUNT(*) = 2             <-- number of category ids

It depends on your tables size and data distribution but I'd guess the first query to be faster.

But if you have lists of various sizes to check (with 3, 4, ... category ids), the first query has to be built dynamically while the second can be easily adjusted.

Join it twice:

select p.*
from products p
join category_relationship c1 on c1.product_id = p.product_id
                             and c1.category_id = 1
join category_relationship c2 on c2.product_id = p.product_id
                             and c2.category_id = 3

Try this:

SELECT DISTINCT product_id FROM category_relationship WHERE category_id IN (1, 3)

For example:

SELECT * 
  FROM Products P, Category_relationship CP
 WHERE P.product_id = CP.product_id
   AND CP.category_id IN (1,3)

SELECT p.product_id, p.product_name
FROM products p LEFT JOIN category_relationship cp ON p.product_id = cp.product_id
GROUP BY p.product_id, p.product_name
HAVING COUNT(cp.category_id) = (SELECT COUNT(*) FROM categories)