enter image description here

i'm trying to filter product with their subcategories : for example : if subcategories = (1,3,4) and if this is subcategory_product :

enter image description here

how can i get the products with subcategories id=(1,3,4) ??!! plz help in this case i should get the product with id 62 .


To limit to only one join you and avoid subqueries can do something like this:

select p.* from product p
join subcategory_product sp on 
    (sp.product_id = p.id and sp.subcategory_id in (1,3,4))
group by p.id
having group_concat(subcategory_id order by subcategory_id separator ',') = '1,3,4';
  • Note that this only returns products with precisely those three subcategories. If the product has those three and one more then it will not return. – mendosi Nov 11 '16 at 10:12
  • @mendosi Wasn't this the requirement? "subcategories id=(1,3,4)" I interpreted this as product has subcategories 1, 3, 4 and only 1, 3, 4. – Károly Nagy Nov 11 '16 at 10:31
  • @KárolyNagy just slightly different interpretation, that is all. – mendosi Nov 11 '16 at 22:01

One approach (you will need to test this for performance if the dataset is large) is this:

Select `sc1`.`product_id`
  From `subcategory_product` As `sc1`
  Join (Select `product_id` From `subcategory_product` Where `subcategory_id` = 3) `sc3`
    On `sc1`.`product_id` = `sc3`.`product_id`
  Join (Select `product_id` From `subcategory_product` Where `subcategory_id` = 4) `sc4`
    On `sc1`.`product_id` = `sc4`.`product_id`
  Where `subcategory_id` = 1

Basically, you're scanning the subcategory_product table once for each of the values, and then doing an INNER JOIN on the results to only get rows back where all three exist.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.