Search code examples
mysqlsqljoincountwhere-in

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

Solution

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

    SELECT  s.id 
from shops s
inner 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