CODEWITHSUNDEEP
mysql
Lee
Lee

Reputation: 1495

MySQL - SELECT where multiple fields must be true

I don't know where to start with this!

I have a product table and a product_attributes table. Each product can have multiple attributes.

If we work with just 2 attributes to keep things simple (id 1 = size, id 2 = colour), the product_attributes table looks like this:

id   product_id   attribute_id   value
======================================

1    1            1              10
2    1            2              Red
3    1            1              12
4    1            2              Red
5    2            1              10
6    2            2              Blue

So here we have 2 products, the first has 2 sizes (10 and 12) both in red. The second is size 10 in blue.

I want to find all products where the size is equal to (10 OR 12) AND the colour is red.

SELECT * FROM product p INNER JOIN product_attribute a ON a.product_id = p.id WHERE (a.value = '10' OR a.value = '12') AND (a.value = 'red')

The above obviously isn't going to work but gives an idea of what I'm after. I'm pretty sure I need a count in here somewhere (WHERE number of hits = 2) but I have no idea where to start.

Any help appreciated.

Upvotes: 0

Views: 86

Answers (2)

ThinkTank
ThinkTank

Reputation: 1191

You can use subquery :

SELECT * 
FROM product p 
INNER JOIN product_attribute a ON a.product_id = p.id 
WHERE a.value = '10' OR a.value = '12'
AND p.id IN (
    SELECT p.id 
    FROM product p 
    INNER JOIN product_attribute a ON a.product_id = p.id 
    WHERE a.value = 'red'    
)

Upvotes: 0

xlecoustillier
xlecoustillier

Reputation: 16351

Something like this should work:

SELECT * 
FROM product p 
WHERE EXISTS (SELECT * 
    FROM product_attribute pa1 
    WHERE pa1.product_id = p.id
        AND pa1.value in ('10', '12'))
AND EXISTS (SELECT * 
    FROM product_attribute pa2 
    WHERE pa2.product_id = p.id
        AND pa2.value = 'Red')

The question @SalmanA asked still applies.

Upvotes: 1

Related Questions