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

MySQL Select IDs Occurring on Different Rows with Multiple Specific Column Values

Problem:

Selecting items from an associative table based on multiple specified values for a particular column is a common but challenging task. For instance, 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

The goal is to select only those items that are in both or all of the specified categories; for example, if category IDs 201 and 202 are provided, only items 1 and 2 should be returned.

Solutions:

1. Self-Join:

This method involves joining the table with itself on the item_id column:

<code class="sql">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 IN (201, 202)</code>

This query returns a joined result set with one row for each unique pair of rows in the original table that satisfy the category_id criteria.

2. Group BY and HAVING:

An alternative approach is to use the GROUP BY and HAVING clauses:

<code class="sql">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</code>

This query groups the rows by item_id and counts the occurrences of category IDs 201 and 202 for each row. The HAVING clause filters out those rows that do not have the specified number of occurrences (in this case, 2).

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