I need to store few items and its properties in form of a key value pairs in the database (mySQL). I am planning to do it as following.
I'll use two tables items and item_properties.
items
itemId | itemName
-------------------
1923 | AC
1235 | Fridge
8273 | Heater
item_properties
itemId | property | value
--------------------------------
1923 | effect | cooling
1923 | consumption | efficient
1923 | type | split
1235 | effect | cooling
1235 | volume | 20 liters
8273 | effect | heating
8273 | consumption | efficient
8273 | heatMethod | coil
Now, if I have to select items whose 'effect' is 'cooling', I can do that using following query (which will give me 'AC' and 'Fridge' in result).
SELECT itemName FROM items i, item_properties p
WHERE i.itemId=p.itemId
AND (p.property = 'effect' AND p.value ='cooling');
I would like to know how write queries to select items that match multiple properties like
select all items whose 'effect' is 'cooling' AND 'consumption' is 'efficient' (which would match item 'AC').
select all items whose 'type' is 'split' OR 'heatMethod' is 'coil' OR 'consumption' is 'effecient' (which would match items 'AC' and 'Heater').
Kindly Help... Thanks in advance!!
解决方案
Here is an example query:
SELECT
itemName
FROM
items i,
JOIN
item_properties effect
ON i.itemId = effect.itemId AND effect.property = 'effect'
JOIN
item_properties consumption
ON i.itemId = consumption.itemId AND consumption.property = 'consumption'
WHERE effect.value = 'cooling' AND consumption.value = 'efficient';
I'll leave the oR query as something you can try yourself. It's simply adding more tables and using OR instead of AND in the WHERE.