What is the correct way of filtering a property by multiple features? Having the following tables:


Property 
Feature 
PropertyHasFeature (property_id, feature_id)


What is the right way to search properties by specifying multiple features? I want to list all properties that have both Feature A and Feature B.

Right now my approach is to join the Property table two times and to add two conditions, like this:

INNER JOIN Feature as feature_1 
ON feature_1.property_id=Property.id

INNER JOIN Feature as feature_2
ON feature_2.property_id=Property.id

WHERE feature_1.id = &lt;ID of first feature&gt; AND feature_2.id = &lt;ID of second feature&gt;


Is this the right way? So if I the users selects 10 features I will have to do 10 joins with the Feature table? Or is there a better way?
It is better to use something like this:

select distinct p.* from Property p
inner join PropertyHasFeature fp on (fp.p_id = p.id)
inner join Feature f on (fp.f_id = f.id)
where f.id in (&lt;ID of first feature&gt;, &lt;ID of second feature&gt;)


In this case you will not need to add the joins when the feature list is getting larger. This will select all the properties having one of the required features.

If you need properties having both features without adding multiple joins, you can try using the following (works for MySQL):

select id, property_text from 
(select p.id as id, p.property_text, count(f_id) as list_count from Property p
 inner join PropertyHasFeature fp on (fp.p_id = p.id)
 inner join Feature f on (fp.f_id = f.id)
 where f.id in (&lt;ID of first feature&gt;, &lt;ID of second feature&gt;)
 group by p_id) as sub
where list_count = 2


To expand the list of the required features, you will have to change the list_count value to the length of the list of your required properties.