How to Select Items Belonging to Multiple Categories in MySQL?

Selecting Multiple Value Matches in a Database Column Using MySQL

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!