How to Efficiently Select Items Belonging to Multiple Categories in a MySQL Table?

Identifying Items in Multiple Categories

Problem:

In a MySQL table containing associative information (item_id and category_id), the goal is to select only items that are present in a specified set of categories.

Example:

Given the table:

+-----------------------+
| item_id | category_id |
+-----------------------+
|   1     |    200      |
|   1     |    201      |
|   1     |    202      |
|   2     |    201      |
|   2     |    202      |
|   3     |    202      |
|   3     |    203      |
|   4     |    201      |
|   4     |    207      |
+-----------------------+

If the category IDs 201 and 202 are passed, the query should return only items 1 and 2, as they are the only ones present in both categories.

Solution 1: Self-Join

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

This technique requires a self-join that creates a Cartesian product of the table, which can be inefficient for large datasets.

Solution 2: GROUP BY and HAVING

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

This solution uses GROUP BY and HAVING to count the number of categories for each item and filter out those that match the specified set. It performs better for larger datasets.

The above is the detailed content of How to Efficiently Select Items Belonging to Multiple Categories in a MySQL Table?. For more information, please follow other related articles on the PHP Chinese website!