How to select items by their values combination

My example table – fruits.

+----------+----------+-------+
| FRUIT    | PROPERTY | VALUE |
+----------+----------+-------+
| APPLE    | COLOR    | GREEN |
| APPLE    | COLOR    | RED   |
| APPLE    | FORM     | ROUND |
| CUCUMBER | COLOR    | GREEN |
| CUCUMBER | COLOR    | LONG  |
| KIWI     | COLOR    | GREEN |
| MANGO    | COLOR    | GREEN |
| MANGO    | FORM     | ROUND |
| TOMATO   | FORM     | ROUND |
+----------+----------+-------+

Each fruit have unlimited properties, but for now from 1 to 2. Each property can have unlimited values. I want to select all GREEN (COLOR) and ROUND (FORM) fruits (its example, in real query fruits can be filtered by dozens of properties). Valid result – apple and mango (not cucumber, not tomato, etc – only fruits who have all of desired properties).

How to write such query?

Answer

You can filter the table for the properties that you want, group by fruit and check in the HAVING clause if all the properties are satisfied:

SELECT fruit
FROM fruits
WHERE (property = 'COLOR' AND value = 'GREEN')
   OR (property = 'FORM' AND value = 'ROUND')
GROUP BY fruit
HAVING COUNT(DISTINCT value) = 2

Or:

SELECT fruit
FROM fruits
WHERE (property, value) IN (('COLOR', 'GREEN'), ('FORM', 'ROUND'))
GROUP BY fruit
HAVING COUNT(DISTINCT value) = 2

See the demo.