How to Select MySQL IDs with Multiple Specific Column Values Across Rows?

Selecting MySQL IDs Occurring Across Multiple Rows with Specific Column Values

Identifying items that satisfy two or more specific values within a specific column can be challenging in MySQL. Consider the following table structure:

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

The goal is to select only items that belong to both designated categories, despite potentially being associated with additional categories.

Ineffective Solutions:

• WHERE category_id = 201 AND category_id = 202: Unsuccessful because it searches for impossible conditions (two values on a single row).
• WHERE category_id = 201 OR category_id = 202: Returns incorrect results (includes items that do not belong to both categories).

Effective Solutions:

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 method joins rows from the same table based on a common column (item_id), allowing for comparisons between multiple rows and their values.

GROUP BY:

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

Utilizing the GROUP BY function, this approach counts the number of distinct category values for each item_id. Items with a cat_count equal to the number of specified categories (2 in this case) are selected.

Conclusion:

Both methods effectively identify items that meet the desired criteria. The choice between them depends on the specific requirements and database performance considerations.

The above is the detailed content of How to Select MySQL IDs with Multiple Specific Column Values Across Rows?. For more information, please follow other related articles on the PHP Chinese website!