0

I am working on a project where I'd like to display products that are subject to certain filters. Let's say I have the following 2 tables:

Products

id   name
--------------
1    'Product 1'
2    'Product 2'

Filters

product_id   filter_id
----------------------
1            a
1            b
2            a

I'm trying to write a query that only returns products if the filters are set. So if filter 'a' is active the results should be product 1 and 2, and if 'a' AND 'b' are active it should ONLY return product 1.

I've tried:

SELECT p.id
FROM products p
LEFT JOIN filters f ON f.product_id = p.id
WHERE filter.id = 'a'
GROUP BY p.id

This returns the id's for product 1 and 2 as expected. However, when I try:

SELECT p.id
FROM products p
LEFT JOIN filters f ON f.product_id = p.id
WHERE filter.id = 'a' AND filter.id = 'b'
GROUP BY p.id

I'd expect it to return the id for product 1, but it returns no results. How can I rewrite my query so that I get the product id's for the active filters? Can this be done with MySQL alone or do I have to loop through the results with php?

Shadow
  • 33,525
  • 10
  • 51
  • 64
Leo
  • 5
  • 2
  • The `id` can't be `a` and `b` at the same time. Likely you meant for an `or`.. you also could use an `in`. – user3783243 Sep 14 '20 at 01:10
  • When I use OR it returns both product 1 and 2. I want the query to only return product 1 when filter 'a' and 'b' are both set. Could you give an example of how to best use IN in this specific case? – Leo Sep 14 '20 at 01:28

1 Answers1

0

Your query just needs a slight change: Use OR instead of AND...change it to this:

SELECT p.id
FROM products p
LEFT JOIN filters f ON f.product_id = p.id
WHERE filter_id in ('a','b')
GROUP BY p.id
HAVING COUNT(*) = no_of_filters
Edrich
  • 175
  • 1
  • 8
  • When I use OR it returns both product 1 and 2. I want the query to only return product 1 when filter 'a' and 'b' are both set. – Leo Sep 14 '20 at 01:23
  • I've edited the question, to include the keyword 'having'...whereby you will specify the number of filters selected, in your case, if you have selected both filters a and b, the no_of_filters should be 2 – Edrich Sep 14 '20 at 01:35
  • Brilliant! Works like a charm! Thanks a lot for your answer, I was breaking my head over this. – Leo Sep 14 '20 at 01:45