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:
开发者_如何学PythonCategories_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)
精彩评论