How to filter for multiple values with multiple JOINs

This is a vague title, so please correct it if you can think of a better one.

Consider these 4 tables:

products: id (int), name, msrp, etc...

subproducts: id (int), product_id (int), name (varchar)

subproducts_properties: id (int), subproduct_id (int), property_id (int)

subproducts_properties_values: subproducts_properties_id (int), value (varchar)

So the basic idea here is that a single product can have multiple subproducts (models), a single subproduct can have multiple properties (or specs), and a single property for a subproduct can have multiple values.

Now imagine that there is a product that has multiple subproducts which have multiple properties which have multiple values. In particular, this product has a subproduct that has these properties:

Property 1 - property_id: 1; value = '.17 HMR';

Property 2 - property_id: 22; value = 'Bolt';

Where property_id 1 has a name Caliber and property_id 2 has a name Action (think: guns).

What this product doesn't have is a subproduct containing a property with property_id=1 and value='5.56 mm NATO'.

The user has drop-down boxes where he can select multiple filter sets based on unique values. So if a user selects a Caliber of .17 HMR and an Action of Bolt, he should expect to see our product, but when he pulls back Bolt and a Caliber of, say, 5.56 mm NATO, he should see no products because our product doesn't match both filters.

So...given this information, I would like to pull back all products (one product per row) in the database and filter by multiple property values. My current attempt goes like this:

SELECT p.*, m.name as manufacturer_name, pt.name as product_type_name, COUNT(DISTINCT com.id) AS num_reviews, ROUND(AVG(com.rating), 1) as rating, pi.image_thumb 
FROM products p 
        LEFT JOIN manufacturers m ON p.manufacturer_id=m.id 
        LEFT JOIN product_types pt ON p.product_type_id=pt.id 
        LEFT JOIN comments com ON p.id=com.object_id AND com.object_group = 'com_products' AND com.level=0 
        LEFT JOIN ( 
                SELECT product_id, thumb_path as image_thumb 
                FROM products_images pi 
                ORDER BY ordering ASC 
            ) AS pi ON p.id=pi.product_id 
        LEFT JOIN subproducts sp ON p.id=sp.product_id 
        LEFT JOIN subproducts_properties spp ON sp.id=spp.subproduct_id 
        LEFT JOIN subproducts_properties_values sppv ON spp.id=spp开发者_运维技巧v.sp_id 
WHERE p.deleted != 1 AND p.published=1 
            AND ( 
                        IF(spp.property_id=1, IF(sppv.value='5.56 mm NATO',1,0), 0) = 1 
                    OR IF(spp.property_id=22, IF(sppv.value='Bolt',1,0), 0) = 1
                ) 
GROUP BY p.id 
ORDER BY p.created DESC 
LIMIT 0, 12

The part to focus on is the last AND in the WHERE clause where I attempt to get the filters going. Notice, too, that I have a GROUP BY in order to be able to perform aggregate functions on other tables. This particular query will pull back our product because of the OR inside that last AND, but I would like to set it up so it doesn't pull it back in this case, but does pull it back if instead of sppv.value='5.56 mm NATO' there is sppv.value='.17 HMR' (which is a value for our subproduct).

I've tried putting an AND in there instead, but it doesn't return anything because each value has its own row in the sppv table.

Please help! I'm totally lost for what to do here.

Thanks in advance!

try a Having - Count - If instead of the where:

WHERE p.deleted != 1 AND p.published=1 
GROUP BY p.id 
HAVING COUNT(IF(spp.property_id = 1 AND sppv.value='5.56 mm NATO',1,NULL)) > 0 
       AND COUNT(IF(spp.property_id=22 AND sppv.value='Bolt',1,NULL)) > 0
ORDER BY p.created DESC