MySQL query advice required

Question

I have the following table representing products and their attributes/properties. I need to write a query that will get the value of the property named Insert Style for products where the First Choice Material is Gold.

I got this far:

SELECT * 
FROM shop_product_properties
WHERE name = 'Insert Size'
AND product_id IN (
    SELECT DISTINCT product_id
    FROM shop_product_properties
    WHERE ((LOWER(name) = 'first choice material'
    AND value = 'Aluminium') AND (LOWER(name) = 'insert style' AND value = 'CCGT'))
);

Answer 1

I need to write a query that will get all value with name Insert Style

So start with that:

SELECT * 
FROM shop_product_properties
WHERE name = 'Insert Style';

where First Choice Material = Gold AND they have the same product_id

Then introduce a subquery:

SELECT * 
FROM shop_product_properties
WHERE name = 'Insert Style'
AND product_id IN (
    SELECT DISTINCT product_id
    FROM shop_product_properties
    WHERE name = 'First Choice Material'
    AND value = 'Gold'
);

http://sqlfiddle.com/#!2/ecfb4/1