Home > Database > Mysql Tutorial > How to Select Items Belonging to Multiple Categories in MySQL?

How to Select Items Belonging to Multiple Categories in MySQL?

Patricia Arquette
Release: 2024-10-31 22:02:02
Original
390 people have browsed it

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
Copy after login

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
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template