jdoe jdoe - 1 month ago 12
MySQL Question

SQL query which return counted results from many tables

I have two tables 'shops' and 'products'. Shop has many products and product belongs to one shop. Moreover product belongs to the only one category. I have id's of 3 categories (for example 1,2,3). How can I get all shops having products which belongs to all 3 categories?

I tried

SELECT distinct s.*
from shops s
left join products p on p.shop_id = s.id
where p.category_id in (1,2,3)


but this returns shops with products which belongs to the category 1 OR 2 OR 3 But I want the products which belongs to the all 3 categories like 1 AND 2 AND 3, so every shop have to have at least 3 products

Answer Source

You could check the s.id having count(distinct p.category_id) = 3

SELECT  s.id 
from shops s
left join products p on p.shop_id = s.id
where p.category_id in (1,2,3)
group by s.id
having count(distinct p.category_id) = 3