CODEWITHSUNDEEP
mysqlcategoriesproduct
user3663448
user3663448

Reputation: 63

mysql products in category select

I have a table named product_cats, the relevant columns are

product_id,
cat_id

Say we have categories 1, 2, 3, 4, and 5.

I need all the product_id's in cat_id 1 and 2 and (3 or 4 or 5).

I have a few working methods at this point, but they're all terribly slow.

Can someone shed some mysql magic on me?

Edit:

So the product needs to be in both category 1 and 2, and in either 3, 4, or 5.

Here's one of my attempts to get the idea better

SELECT
  p1.product_id
FROM
  product_cats p1 JOIN product_cats p2 ON ( p2.product_id = p1.product_id )  
  JOIN product_cats p3 ON ( p3.product_id = p1.product_id )  
  JOIN product_cats p4 ON ( p4.product_id = p1.product_id )   
WHERE
  (p1.category_id=? AND p2.category_id=? AND p3.category_id=?)
  OR 
  (p1.category_id=? AND p2.category_id=? AND p4.category_id=?)

Upvotes: 4

Views: 422

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

Using Mysql you can implement your logic by using group by and sum function with having clause

select product_id
from product_cats
group by product_id
having sum(cat_id = 1) > 0
and sum(cat_id = 2) > 0
and sum(cat_id in(3,4,5)) > 0

Above query will return product_ids which has cat_id 1 and cat_id 2 and cat_id can be 3,4,5

Upvotes: 1

fthiella
fthiella

Reputation: 49089

To select the products that are both in category 1 and 2 you can use a query like this:

select
  product_id
from
 product_cats
where
  cat_id in (1,2)
group by
  product_id
having
  count(distinct cat_id)=2;

an index on both product_id and cat_id should help improve performances:

alter table product_cats add index idx_product_cats (product_id, cat_id);

Upvotes: 2

Related Questions