sql - 如何使用多个 JOIN 过滤多个值

Question

这是一个模糊的标题，所以如果你能想到一个更好的标题，请更正它。
考虑这 4 个表:
产品 : id (int)、name、msrp 等...
子产品 : id (int), product_id (int), name (varchar)
子产品属性 : id (int), subproduct_id (int), property_id (int)
子产品属性值 : subproducts_properties_id (int), value (varchar)
所以这里的基本思想是一个产品可以有多个子产品(型号)，一个子产品可以有多个属性(或规范)，一个子产品的一个属性可以有多个值。
现在想象有一个产品具有多个子产品，这些子产品具有多个属性，这些属性具有多个值。特别是，该产品具有具有以下属性的子产品:

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

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

在哪里 property_id 1有一个名字 Caliber和 property_id 2有一个名字 Action (想想:枪)。
这个产品没有的是一个包含 property_id=1 属性的子产品。和 value='5.56 mm NATO' .
用户有下拉框，他可以在其中根据唯一值选择多个过滤器集。因此，如果用户选择 Caliber的 .17 HMR和 Action的 Bolt ，他应该期待看到我们的产品，但是当他退后 Bolt和 Caliber例如， 5.56 mm NATO ，他应该看不到任何产品，因为我们的产品与两个过滤器都不匹配。
所以...鉴于此信息，我想撤回数据库中的所有产品(每行一个产品)并按多个属性值进行过滤。我目前的尝试是这样的:

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=sppv.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

重点关注的部分是最后 AND在 WHERE我试图让过滤器运行的子句。还要注意，我有一个 GROUP BY为了能够在其他表上执行聚合函数。由于 OR，此特定查询将撤回我们的产品。最后的里面 AND , 但我想将其设置为在这种情况下不会将其拉回，但如果不是 sppv.value='5.56 mm NATO' 则将其拉回有 sppv.value='.17 HMR' (这是我们的子产品的一个值)。
我尝试在其中放置一个 AND，但它不返回任何内容，因为每个值在 sppv 表中都有自己的行。
请帮忙!我完全不知道在这里做什么。
提前致谢!

Answer 1

尝试一个 Have - Count - If 而不是 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