MySQL select products by filter

Question

I need to select IDs of products which have specific value_id.

Table products:

| product_id | product_name | category_id | active |
| 125        | notebook1    | 3           | 1      |
| 236        | notebook2    | 3           | 1      |

Table filters:

| product_id | value_id | value_name |
| 125        | 35       | 15" display|
| 125        | 36       | 8GB RAM    |
| 236        | 35       | 15" display|

This select works ok, if I want to select products IDs by one value_id:

SELECT DISTINCT p.product_id FROM products p
LEFT JOIN filters f ON (p.product_id=f.product_id)
WHERE p.active=1 AND p.category_id=3 AND f.value_id=36;

But when I check more filters on web, I need to select by more values, problem is that when I use:

SELECT DISTINCT p.product_id FROM products p
LEFT JOIN filters f ON (p.product_id=f.product_id)
WHERE p.active=1 AND p.category_id=3 AND f.value_id IN(35,36);

It gives me products which have 15" display OR 8GB RAM, I need products which have 15" display AND 8GB RAM. Thanks.

Answer 1

I assume since you mentioned web that you are using a program language to generate the queries. I am also going to assume that you do not have control over how the data is structured in the tables so we have to have an option that will work given the table structure provided.

The below option works but will get sloppy if you are dealing with large numbers of options being passed in.

Option Using Subsequent Joins for Each Option

SELECT 
    DISTINCT p.product_id 
FROM 
    products p
JOIN filters f1 
    ON p.product_id=f1.product_id
    and f1.value_id = 35
JOIN filters f2 
    ON p.product_id=f2.product_id
    and f2.value_id = 36
WHERE 
    p.active=1 
    AND p.category_id=3
;

If you get your filter value ids in a comma delimited list and want to use it in that fashion (hints your IN statement) you can take this approach. I am making the assumption that you know the total number of filter values passed in. In your example you had 2 so the query would like this.

Group Count Equal to Filters Provided w/ IN Statement

SELECT 
    p.product_id
FROM 
    products p
JOIN filters f 
    ON p.product_id=f.product_id
    AND p.active=1 
    AND p.category_id=3 
    AND f.value_id IN(35,36)
GROUP BY
    p.product_id
HAVING
    COUNT(p.product_id) = 2

Also putting as much in your join condition rather than the where clause will help speed up the query since the from clause is evaluated before the where clause.