Problem:
You have an associative table where items can belong to multiple categories. How can you select only the items that belong to a specified set of categories?
Solution:
To select items that satisfy multiple specific values for a column, there are two primary approaches in MySQL:
Self-Join Method:
This method compares multiple rows from the same table in one join operation. For example, to select items in categories 201 and 202:
SELECT c1.item_id FROM item_category AS c1 INNER JOIN item_category AS c2 ON c1.item_id = c2.item_id WHERE c1.category_id = 201 AND c2.category_id = 202
GROUP BY Method:
This method groups rows by a column and uses the COUNT() aggregate to verify if an item belongs to all specified categories. For example, to select items in categories 201 and 202:
SELECT c.item_id, COUNT(*) AS cat_count FROM item_category AS c WHERE c.category_id IN (201,202) GROUP BY c.item_id HAVING cat_count = 2
The choice between these two methods depends on the number of categories you are searching for and performance considerations.
The above is the detailed content of How to Select Items Belonging to Multiple Categories in MySQL?. For more information, please follow other related articles on the PHP Chinese website!