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:
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!