SELECT query where 1 column equals 2 variables

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.

Answer

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 |