CODEWITHSUNDEEP
mysqlsql
user101289
user101289

Reputation: 10422

SELECT from table WHERE value across multiple rows

I have a table that maps products to categories. Each product can belong to multiple categories, so a product can have multiple rows with different category IDs. It looks something like this:

CREATE TABLE `products_mid` (
  `id` int(11) UNSIGNED NOT NULL,
  `product_id` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `category_id` int(11) UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

If I want to get a list of all product IDs that are in category A that's simple enough-- but is there a way to get all product_ids where they belong to categories A, B and C as simply as possible?

Upvotes: 1

Views: 60

Answers (4)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Another way:

SELECT product_id
FROM products_mid
WHERE category_id IN (1, 2, 3)
GROUP BY product_id
HAVING COUNT(DISTINCT category_id) = 3

This would find all products in categories 1 through 3, although a product could also belong to other categories. If you want products which exclusively belong to category 1 through 3, and no others, then we would have to rephrase the query.

Upvotes: 1

NineBerry
NineBerry

Reputation: 28499

Use the intersect operator to find the common values of three separate queries.

select product_id from products_mid where category_id = 1
intersect
select product_id from products_mid where category_id = 2
intersect
select product_id from products_mid where category_id = 3

Upvotes: 1

LoztInSpace
LoztInSpace

Reputation: 5697

It will be something like this:

select product_id from products_mid
where
category_id in (1,2,3)
group by product_id
having count(distinct category_id)=3

Upvotes: 2

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726539

A better approach is to do it with HAVING clause, not a WHERE clause. Group by product_id, and select rows having non-zero count of each of your three categories, like this:

SELECT product_id
GROUP BY product_id
HAVING SUM(CASE WHEN category_id=1 THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN category_id=2 THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN category_id=3 THEN 1 ELSE 0 END) > 0

The above statement selects product_ids of products that belong to all three categories with IDs 1, 2, and 3.

Upvotes: 3

Related Questions