Find all categories that have some specific features

I have a table containing some categories

Categories Table: 
-- category_id   <-- primary key
-- name

I also have a table containing some features

Features Table: 
-- feature_id   <-- primary key
-- name

And the many-to-many relation table:

开发者_如何学Python

Categories_Features Table: 
-- category_feature_id   <-- primary key
-- category_id
-- feature_id

Now I want to find all the categories that have some multiple specific features. For example all the categories that have both features with id 3 AND 10.

Here is the sample query to find all categories that has the feature with id 10:

select * from categories inner join categories_features on(categories.category_id = categories_features.category_id) where feature_id = 10

---

Here is a solution:

SELECT * FROM `categories` 
WHERE
category_id in (select category_id from categories_features where feature_id = 3) 
AND
category_id in (select category_id from categories_features where feature_id = 10)