SELECT query where 1 column equals 2 variables

Question

The issue: I want to select the products which have the option_value_id of both 1 and 3. But, as you can see, it will also show the products which have only have 1 of the option_value_ids.

I tried adding AND instead of IN but that will obviously show no results.

The answer might be simple, but I just can't seem to figure it out at the moment. Could someone help me out? Even a small hint can be appreciated.

enter image description here

Not sure if @bonCodigo is being sarcastic, but it's PHPMyAdmin 3.5.2.2. — user1961685, Jan 09 '13 at 15:33
oh, ofcourse I wasn't sarcastic at all. It really is catchy. And I was even wondering if you are running it on a mac... ;) (if there's a possibility to run MYSql on mac..) — bonCodigo, Jan 09 '13 at 15:35
Nope, I never worked with a Mac. You should be able to run PHPMyAdmin on any web server, as long as it has MySQL running. There's LAMP/MAMP for Mac. But I guess this goes a bit off-topic hehe. — user1961685, Jan 09 '13 at 15:39

Mahmoud Gamal · Accepted Answer · 2013-01-09T15:22:26.090

4

This is called Relation Division, and here is one way to do so:

SELECT *
FROM TABLEName 
WHERE Product_ID IN(SELECT Product_ID
            FROM Tablename
            WHERE option_value_id IN(1, 3)
            GROUP BY Product_ID
            HAVING COUNT(option_value_id) = 2);

SQL Fiddle Demo

This will give you:

| ID | PRODUCT_ID | OPTION_VALUE_ID |
-------------------------------------
|  1 |          1 |               1 |
|  3 |          1 |               3 |
| 13 |          2 |               3 |
| 14 |          2 |               1 |

edited Jan 09 '13 at 15:22

answered Jan 09 '13 at 15:10

Mahmoud Gamal

78,257
17
139
164

Thank you for the reply, but it should return the product_id 1, 2 and 12. As you can see, product_id 1, 2 and 12 have option_value_id 1 and 3. – user1961685 Jan 09 '13 at 15:16
@user1961685 product_id 12 doesn't have both 1 and 2 it has only 3 in the sample data. See my edit. – Mahmoud Gamal Jan 09 '13 at 15:23

score 0 · Answer 2 · answered Jan 09 '13 at 15:12

This is example of looking at things as a set. I think the best approach is to use SQL's aggregation, particularly the having clause. In MySQL syntax, this looks like:

select pa.product_id
from Product_Attributes pa
group by pa.product_id
having max(pa.option_value_id = 1) = 1 and
       max(pa.option_value_id = 3) = 1

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

This is a common problem, called Relational Division, there is even a tag in SO for it: sql-match-all

Usually, there is a unique constraint on the (product_id, option_value_id), so one solution is to use 2 joins (N joins if you want to check for N attributes):

SELECT p.*                 -- whatever columns you need 
FROM product AS p          -- from the `product` table
  JOIN products_attributes AS pa1
    ON  pa1.option_value_id = 1
    AND pa1.product_id = p.product_id
  JOIN products_attributes AS pa2
    ON  pa2.option_value_id = 3
    AND pa2.product_id = p.product_id ;

There is a similar question, with more than 10 different ways to achieve the same result (and benchmarks for Postgres): How to filter SQL results in a has-many-through relation

score 0 · Answer 4 · answered Jan 09 '13 at 15:30

0

Try this:

SELECT * 
FROM products_attributes 
WHERE option_value_id IN (1, 3) 
GROUP BY product_id 
HAVING COUNT(*) = 2

answered Jan 09 '13 at 15:30

Saharsh Shah

28,687
8
48
83

SELECT query where 1 column equals 2 variables

4 Answers4

SQL Fiddle Demo