How to select records who match criteria defined in a join table?

Question

I have this three tables:

products TABLE:
id:integer
name:string

features TABLE:
id:integer
name:string

features_products TABLE:
product_id:integer
feature_id:integer

The features_products TABLE tells me which features have each product. For example:

product_id feature_id
   1         3
   1         5
   3         4

tells me the product 1 has features 3 and 5 and product 3 have feature 4, also, product 2 (if exists) doesn't have features.

My question is, how can I SELECT all the products from the products TABLE wich have determinated features? For example, SELECT products which have features 3 AND 5, or SELECT products which have feature 2

Answer 1

Something similar to this:

select * from product
where id in ( select product_id from feature_products where feature id in ( 1,3 ) )

swap out the (1,3) for the features you want to include.

Answer 2

To select all the product ids for products which have both features 3 and 5:

SELECT product_id
FROM features_products
WHERE feature_id IN (3, 5)
GROUP BY product_id
HAVING COUNT(*) = 2

This assumes that there is a uniqueness contraint on (product_id, feature_id). If you want the entire row from the product table then use this in a subquery:

SELECT *
FROM products
WHERE product_id IN (
    SELECT product_id
    FROM features_products
    WHERE feature_id IN (3, 5)
    GROUP BY product_id
    HAVING COUNT(*) = 2
)

Answer 3

You could do the following the join those tables and get the data you need:

SELECT *
FROM products p JOIN features_products fp ON p.id = fp.product_id
                JOIN features f ON f.id = fp.feature_id
WHERE f.id = 3 OR f.id = 5