mysql AND clause on same column multiple times

I am trying to retrieve properties which has multiple amenities.

But th开发者_开发百科e table structure is: properties are in property table and amenities are associated with the property.

Amenities table has:

id property_id amenity_name ....

Now i want to search the amenities that have 'parking' and 'elevator' and so on.

select property_id from amenities where name = 'parking' and name = 'elevator';

I dont want to fire a join query for this.

So can anybody save me in this situation?

Try this:

SELECT property_id FROM amenities
WHERE amenity_name IN ('parking', 'elevator')
GROUP BY property_id 
HAVING COUNT(amenity_name) >= 2

Here's what's going on...

First, get the list of Property ID's that have the amenities you're interested in:

SELECT property_id FROM amenities WHERE amenity_name IN ('parking', 'elevator')

Then narrow the list to include only those Property ID's which have more than one amenity. Grouping by property_id bundles the results into unique groups based on which property_id they belong to. Then, we simply count the number of amenity_names in each group and check if it's 2 or greater.

GROUP BY property_id HAVING COUNT(amenity_name) >= 2

One assumption in the code above is that you won't have the same amenity_name associated with a property_id more than once. To eliminate issues related to that, change the HAVING clause to include DISTINCT to weed out the duplicates, but don't add it unless you need it.

GROUP BY property_id HAVING COUNT(DISTINCT(amenity_name)) >= 2

select property_id 
from amenities 
where name in ('parking','elevator')
group by property_id
having count(distinct(name)) = 2

Try this -:

   scope :amenity_type, lambda {|term|
          where("amenities.name" => term).joins(:amenities).group("amenities.property_id").having("count(amenities.name) >= #{term.size}") unless term.blank?
   }