CODEWITHSUNDEEP
mysqlsql
Zbigniew Ledwoń
Zbigniew Ledwoń

Reputation: 682

How to construct MySQL query to get Products with all selected Features?

SELECT * 
FROM products p 
INNER JOIN features_products fp ON p.id = fp.product_id 
WHERE fp.feature_id in (1,5,11)

This query returns all Products that have at least one of given Feature ids (1 or 5 or 11).

I need is a list of Products that have all 3 (1 and 5 and 11)

Help!

Upvotes: 0

Views: 451

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

This is an example of a set-within-sets query. I think the best approach is aggregation, because it provides the most flexibility. Here is how you would find the products with all three features:

SELECT p.*
FROM products p INNER JOIN
     features_products fp
     ON p.id = fp.product_id
group by p.id
having sum(fp.feature_id = 1) > 0 and
       sum(fp.feature_id = 5) > 0 and
       sum(fp.feature_id = 11) > 0;

Each condition in the having clause is checking for the existence of one product. What makes this flexible is if you wanted to have inclusion and exclusion lists. For instance, always has 1 and 5, but never 11:

SELECT p.*
FROM products p INNER JOIN
     features_products fp
     ON p.id = fp.product_id
group by p.id
having sum(fp.feature_id = 1) > 0 and
       sum(fp.feature_id = 5) > 0 and
       sum(fp.feature_id = 11) = 0;

Upvotes: 3

Related Questions