mysql 一对多表,MySQL：查询一对多表？

What would be the appropriate way to query products with a specific property in the following database design with a one-to-many approach?

I guess that I should be doing something like the following:

SELECT (*) FROM productProperties WHERE property = 'weight' AND value = '10'

But what if I need to products that has both weight = 10 & color = blue in the same query?

Example of database design:

table: products

------------------------

id | name | price

------------------------

0 | myName | 100

1 | myName2 | 200

table: productProperties

------------------------------------------------

product | property | Value

------------------------------------------------

0 | weight | 10

1 | weight | 20

1 | color | blue

解决方案What if I need to products that has

both weight = 10 & color = blue in the

same query?

One option:

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

Another option with subqueries:

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'

)