Search for products with multiple criteria

Question

I'm having a problem with fetching some data.
The tables I have (for testing purposes):

Products:

product_id      | product_name
-----------------------------
1               | product 1
2               | product 2
3               | product 3

Attributes:

product_id      | attribute_id      | attribute_value
-------------------------------------------------------
1               | 1                 | lorem
1               | 2                 | ipsum
2               | 1                 | lorem
2               | 2                 | doler
3               | 1                 | sit
3               | 2                 | ipsum

I want to find the products that have lorem stored in attribute_id 1 AND ipsum stored in attribute_id 2.

If I use this query,

SELECT
   attributes.attribute_id,
   attributes.attribute_value,
   products.product_id
FROM
   products
   Inner Join attributes ON products.product_id = attributes.product_id
WHERE
   (attributes.attribute_id = 1 AND attributes.attribute_value = 'lorem')
OR
   (attributes.attribute_id = 2 AND attributes.attribute_value = 'ipsum')

I get:

attribute_id         | attribute_value     | product_id
----------------------------------------------------------
1                    | lorem               | 1
2                    | ipsum               | 1
1                    | lorem               | 2
2                    | lorem               | 3

But I really want to get this result:

attribute_id_1 | attribute_id_2 | attribute_value_1 | attribute_value_2 | product_id
----------------------------------------------------------
1              | 2              | lorem             | ipsum             | 1

Or just product_id 1 as a result.

I can get it working with the next query, but in production (with a lot of data and a few more joins) this is WAY to slow.

SELECT
   products.product_id
FROM
   products
   Inner Join attributes ON products.product_id = attributes.product_id
WHERE
   attributes.attribute_value = 'ipsum'
AND
   products.product_id IN (
      SELECT
         products.product_id
      FROM
         products
         Inner Join attributes ON products.product_id = attributes.product_id
      WHERE
         attributes.attribute_value =  'lorem'
)

I hope you understand what I'm trying to do.

Can someone please help me?
Thanks!

Answer 1

Could you take the first (broken) query, group by product_id and then add a HAVING COUNT(*) = 2 clause?

Answer 2

Quote:

attribute_id_1 | attribute_id_2 | attribute_value_1 | attribute_value_2 | product_id
----------------------------------------------------------
1              | 2              | lorem             | ipsum             | 1

I think you can't create dynamic fields.. i don't see why you would fetch your data that way? Why don't you simply do something like :

select Products.* from Products,Attributes WHERE
Products.product_id = Attributes.product_id
AND ((Attributes.attribute_value='ipsum' AND Attributes.attribute_id='1') OR (Attributes.attribute_value='lorem'  AND  Attributes.attribute_id='2')) GROUP by Products.product_id;

Answer 3

SELECT * FROM products p
INNER JOIN attributes a USING (product_id)
WHERE a.attribute_value IN ('lorem','ipsum')

Answer 4

Since the first four columns are all fixed, there's no need to select them. All you need is the product id; you can get this by...

SELECT product_id
    FROM products
    WHERE EXISTS (SELECT *
                      FROM attributes
                      WHERE attributes.product_id      = products.product_id AND
                            attributes.attribute_id    = 1                   AND
                            attributes.attribute_value = 'lorem')
                  AND
          EXISTS (SELECT *
                      FROM attributes
                      WHERE attributes.product_id      = products.product_id AND
                            attributes.attribute_id    = 2                   AND
                            attributes.attribute_value = 'ipsum');

If you really must have the fixed columns in your output, you could try...

SELECT 1 AS attribute_id_1,
       2 AS attribute_id_2,
       'lorem' AS attribute_value_1,
       'ipsum' AS attribute_value_2,
       product_id
    FROM ...etc...

Answer 5

Select 
a1.attribute_id attribute_id_1,  
a2.attribute_id attribute_id_2,
a1.attribute_value attribute_value_1,
a2.attribute_value attribute_value_2,
p.product_id
from products p
inner join attributes a1 on a1.product_id = p.product_id and a1.attribute_id = 1 
inner join attributes a2 on a2.product_id = p.product_id and a2.attribute_id = 2
where a1.attribute_value='lorem'
and a2.attribute_value='ipsum';