Filter by multiple values on the same column

Question

What follows is a simplified version of the tables and the task itself, if it's of any help the actual tables are from the Opencart's database, so what I want to achieve is to filter products in store by manufacturer and attributes. All I need is a hint to how this can be achieved in sql not necessarily opencart specific. Trying to learn SQL so please don't advise using modules.

Tables:

product
-------------------
ID name description manufacturer_id
0  n0   desc0       33
1  n1   desc1       56
2  n2   desc2       68

product_attribute
-------------------
pID ID text
0   12 red 
0   13 xl
1   12 red
1   13 xs
2   13 xxl

SQL to filter the products by manufacturer and attribute, the condition between attribute groups (ex. 'color, size, ..') should be AND, the condition between the attributes of the same group (ex. 'color') should be OR. Say I want to get the products that have the manufacturer (33 OR 56) AND color 'red OR green' and a size of 'xl OR xxl':

---------------
Manufacurer
    ✓ 33
    ✓ 56
    o 68

Color
    ✓ red
    ✓ green

Size
    ✓ xl
    o xs
    ✓ xxl
---------------

SELECT p.ID
FROM product p
LEFT JOIN product_attribute pa ON (pa.pID = p.ID)
WHERE p.manufacturer_id = 33 OR p.manufacturer_id = 56
AND   pa.text = red OR pa.text = green
AND   pa.text = xl  OR pa.text = xxl

Should return:

result table
--------------
ID
0

Answer 1

The logicals operations have priorities like in regular math.

The AND operator has the priority over the OR the same way the multiplication has priority over the addition.

Also, since you're working with strings, don't forget to use double or simples quotes.

p.manufacturer_id = 33 OR p.manufacturer_id = 56
AND   pa.text = "red" OR pa.text = "green"
AND   pa.text = "xl"  OR pa.text = "xxl"

Will provide the same result than

p.manufacturer_id = 33
OR (p.manufacturer_id = 56 AND pa.text = "red")
OR (pa.text = "green" AND pa.text = "xl")
OR pa.text = "xxl"

I suppose the result of your query in your example is

result table
--------------
ID
0
1
2

I suggest you to use parenthesis to make sure your conditions are well respected.

(p.manufacturer_id = 33 OR p.manufacturer_id = 56)
AND (pa.text = "red" OR pa.text = "green")
AND (pa.text = "xl"  OR pa.text = "xxl")

The above query won't work, because for an unique entry, if (pa.text = "red" OR pa.text = "green") is true, then (pa.text = "xl" OR pa.text = "xxl") will be false (since pa.text value is already "red" or "green")

Since you need entries that have red or green color AND xl or xxl size, you can search for the entries that have 2 of thoses I assume that a product can't be green and red at the same time and can't have 2 differents sizes

SELECT p.ID
FROM product p
LEFT JOIN product_attribute pa ON (pa.pID = p.ID)
WHERE (p.manufacturer_id = 33 OR p.manufacturer_id = 56)
AND pa.text IN ("red", "green", "xl", "xxl")
GROUP by pa.text
HAVING COUNT(*) = 2

Result

ID
--
1

1 because I tested it with MySQL and it starts the auto incremented indexes with 1.

Test it yourself