CODEWITHSUNDEEP
mysqlrelational-database
Timmeh
Timmeh

Reputation: 135

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:

Upvotes: 1

Views: 323

Answers (5)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115540

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.

Upvotes: 2

Denis de Bernardy
Denis de Bernardy

Reputation: 78463

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

Upvotes: 1

Hck
Hck

Reputation: 9167

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)

Upvotes: 0

Johann Blais
Johann Blais

Reputation: 9469

For example:

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

Upvotes: 0

Femi
Femi

Reputation: 64700

Try this:

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

Upvotes: 0

Related Questions