CODEWITHSUNDEEP
mysqlsql
Oogway
Oogway

Reputation: 21

SQL Filtering by category

pro_id | Category
--------+---------
  25   | electronics
  27   | Electrical
  25   | household
  27   | electronics
  25   | industrial

Above is my database table. I want to filter the results by category. If "electronics" is the selected category, the results should include product ids 25 and 27. This is easy. But I want to filter by multiple categories. For example if I select "electronics" and "household", the result should be only product id 25.

Upvotes: 1

Views: 3818

Answers (3)

DRapp
DRapp

Reputation: 48139

If you want only products that match BOTH criteria, you'll need a HAVING clause...

SELECT
      p.pro_id
   FROM       
      prod p
   where
      p.category in ('electronics', 'household')
   having
      count(*) = 2

The benefit of this is if you wanted 3 or more categories, all you have to do is add them to the "IN" clause and change the count to 3, 4 or whatever...

Upvotes: 2

biziclop
biziclop

Reputation: 14596

See example here: http://sqlfiddle.com/#!2/09028/7

Join your table to itself by pro_id, then filter by categories to select all product having both categories:

SELECT prod1.pro_id
FROM       prod AS prod1
INNER JOIN prod AS prod2 ON prod1.pro_id = prod2.pro_id
WHERE prod1.Category = 'electronics'
  AND prod2.Category = 'household';

Select all product having at least one category:

SELECT prod.pro_id
FROM  prod
WHERE prod.Category = 'electronics'
   OR prod.Category = 'household'
GROUP BY prod.pro_id;

Upvotes: 2

gtr1971
gtr1971

Reputation: 2732

No it shouldn't, because you have multiple IDs sharing the same Category name. The only way you're going to get what you want is to have another field that uniquely identifies each record, otherwise you have a mess on your hands with repeating data.

Upvotes: 0

Related Questions