title: MySQL query - complex count condition
body: <p>I've a table "location" with the structure:</p>
  
  <pre><code>id  | property_id | location_type
  1   | 1           | 1
  2   | 1           | 2
  3   | 2           | 1
  4   | 3           | 2
  5   | 4           | 1
  6   | 4           | 2
  </code></pre>
  
  <p>I've another table "amenities" with the structure:</p>
  
  <pre><code>id  | property_id | amenity_type
  1   | 1           | 1
  2   | 1           | 3
  3   | 2           | 2
  4   | 3           | 4
  5   | 4           | 1
  6   | 4           | 3
  </code></pre>
  
  <p>I've another table "property" with the structure:</p>
  
  <pre><code>id  | property_id | property_type
  1   | 1           | 2
  2   | 1           | 3
  3   | 2           | 2
  4   | 3           | 4
  5   | 4           | 2
  6   | 4           | 3
  </code></pre>
  
  <p>id - is the primary key of the respective table. property_id is the property ID of my database (foreign key). location_type is </p>
  
  <pre><code>beach (value - 1), mountain (value - 2).
  
  amenity_type is car (value - 1), bike (value - 2), football (value - 3).
  
  property_type is villa (value - 2), house (value - 3)
  </code></pre>
  
  <p>I'm using the following SQL query to select the property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1 i.e. a property has beach and mountains and car and villa and house:</p>
  
  <pre><code>SELECT p.id  
  FROM 
      property AS p           
    JOIN
      location AS l1
          ON  l1.property_id = p.id  
          AND l1.location_type = 1 
    JOIN
      location AS l2
          ON  l2.property_id = p.id  
          AND l2.location_type = 2 
    JOIN                      
      amentities AS a1
          ON  a1.property_id = p.id
          AND a1.amenity_type = 2                 
    JOIN
      properties AS p1
          ON  p1.property_id = p.id  
          AND p1.property_type = 3 
    JOIN
      properties AS p2 
          ON  p2.property_id = p.id  
          AND p2.property_type = 1 
  </code></pre>
  
  <p>suppose I get the count of (property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1) as 1500. I need to get the count with same condition and other property_type, location_type, amenity_type. </p>
  
  <p>But I'm not able to get the count for the following conditions using the above query:</p>
  
  <ol>
  <li><p>count of (property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1) AND location_type = 3</p></li>
  <li><p>count of (property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1) AND location_type = 4</p></li>
  <li><p>count of (property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1) AND amenity_type = 2</p></li>
  <li><p>count of (property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1) AND amenity_type = 3</p></li>
  </ol>
  
  <p>Is there any efficient way to get the count with different location_type, amenity_type, etc.</p>
  
  <p>Please refer to my earlier question - <a href="https://stackoverflow.com/questions/7053627/mysql-query-complex-searching-condition">MySQL query - complex searching condition</a></p>