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'
)