Search code examples
mysqlsqlaggregate-functionshaving-clause

Query to get rows by multiple values

I have table for example like this:

id_product id_feature_value
1 60
2 60
1 40
2 10

I want get distinct id_products where id_feature_value = 60 and id_feature_value = 40 Its just example because I will need filter by more feature_values.

Solution

  • If you want products that have both features, you can use group by and having:

    select id_product
from mytable
where id_feature_value in (40, 60)
group by id_product
having count(*) = 2

    This assumes no duplicates. Otherwise, you need count(distinct id_feature_value) = 2.

    You can easily extend that to handle more features per product: you just need to expand the in clause, and modify the literal number in the having clause accordingly.