Szerintem elég lesz 1 join is.
SELECT pef.products_id
FROM products_to_products_extra_fields pef
LEFT JOIN products p ON p.products_id = pef.products_id
WHERE
(pef.products_extra_fields_id = 2 AND pef.products_extra_fields_value = 'piros') OR
(pef.products_extra_fields_id = 4 AND pef.products_extra_fields_value = '3')
GROUP BY pef.products_id
HAVING count(*) = 2
Jester
