Mysql return only items that have n number of property matches

Question

I have a database of items and each item has various number of properties. Is it possible for MySql only to return items that have a certain number of matches (not properties) when a search is run?

Example: I am searching for any item with a wheel that is red and has a tire. This would return all items with these three matches even if they have more properties and would automatically exclude anything that has less than 3 matches.

I have tried playing with the COUNT + GROUP BY + HAVING but I was unable to put together a meaningful working code. Before I spend more time on this I would like to know if it is possible at all.

TABLE DESIGN

ID ITEM PROPERTY
1  1    red
2  1    wheel
3  1    tire
4  2    red
5  2    wheel
6  2    tire
7  2    lamp
8  3    red
9  3    wheel
10 4    red

I would like it to return ITEM 1 and 2

Answer 1

You would do this with a group by and having. You really provide no information about your data structure, but the basic idea is:

select ip.item
from design ip
where ip.property in ('wheel', 'red', 'tire')
group by ip.item
having count(distinct ip.property) = 3;