CODEWITHSUNDEEP
phpmysql
Michal Tinka
Michal Tinka

Reputation: 165

MySQL select products by filter

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.

Upvotes: 4

Views: 2195

Answers (3)

Derek McCallum
Derek McCallum

Reputation: 111

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.

Upvotes: 7

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Give this a try:

SELECT product_id FROM filters
WHERE value_id IN (35, 36)
GROUP BY product_id
HAVING COUNT(DISTINCT value_id) = 2

This will output 125.

Note that if a given product_id can not have the same value_id more than once then you can remove the DISTINCT keyword.

Fiddle here.

The complete query based on your last one is:

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

Upvotes: 0

Jhonathan H.
Jhonathan H.

Reputation: 2713

If you need an output of 15" display AND 8GB RAM.

    SELECT product_id FROM products
WHERE product_id IN(SELECT DISTINCT product_id from filters WHERE value_id IN(35,35) GROUP BY value_name)

SQL FIDDLE DEMO

Upvotes: 0

Related Questions