Need a MySQL query for selecting from a table storing key value pairs

Question

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!!

Answer 1

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.