mySQL：查询一对多表？

Question

在以下数据库设计中使用一对多方法查询具有特定属性的产品的适当方法是什么？

我想我应该做如下的事情： SELECT (*) FROM ProductProperties WHERE property = 'weight' AND value = '10'

但是如果我需要重量 = 10 和 value = '10' 的产品怎么办？在同一查询中 color = blue？

数据库设计示例：

表：产品

------------------------
id    | name     |  price
------------------------
0     | myName   |  100
1     | myName2  |  200

表：productProperties

------------------------------------------------
product  | property     |  Value
------------------------------------------------
0        | weight       |  10
1        | weight       |  20
1        | color        |  blue

Answer 1

如果我需要的产品有
两者的重量 = 10 &颜色 = 蓝色
相同的查询？

一个选项：

select product, name
  from products inner join productProperties
    on (products.id = productProperties.product)
 where (property = 'weight' and value = '10')
    or (property = 'color' and value = 'blue')
 group by product, name
having count(1) = 2

另一种带有子查询的选项：

select id, name
  from products p
 where exists (
         select 1
           from productProperties pp1
          where p.id = pp1.product 
            and pp1.property = 'weight'
            and value = '10'
       )
   and exists (
         select 1
           from productProperties pp2
          where p.id = pp2.product 
            and pp2.property = 'color'
            and value = 'blue'
       )

Answer 2

SELECT * FROM productProperties p 
WHERE (SELECT COUNT(*) FROM productProperties p1 WHERE p1.product = p.product AND 
( (property = 'weight' AND value = '10') OR (property = 'color' AND value = 'blue') ) 
 =2