title: MYSQL multi argument search
body: <p>I'm having trouble running a multi-argument search query in MySQL:</p>
  
  <pre><code>    SELECT item.* from item, attr, attr_xref
      WHERE attr_xref.item = item.id
      AND attr.id = attr_xref.attrID
      AND (
        (attr.name = 'city' AND attr.val LIKE '%new york%')
        OR
        (attr.name = 'floor' AND attr.val = 6)
      )
  </code></pre>
  
  <p>The query is generated dynamically with php, no trouble there.</p>
  
  <p>The problem is though that this query will not return a strict search. It will return items that have either city or floor equal to their search values.</p>
  
  <p>As I can see it the problem is here <code>AND attr.id = attr_xref.attrID</code> This line returns more than one value for items that have more than 1 attribute, removing the <code>OR</code> operator I'll be trying to match a single attribute with several different names and values, but with one ID.</p>
  
  <p>Is there any way to overcome this?</p>
  
  <p>This is a raw example and at runtime I may have more than 2,3 search arguments.</p>
  
  <p>Thank You, looking froward to your help!</p>
  
  <p><strong>EDIT:</strong>
  Got it working by using the SQL query posted in the accepted answer, and just added this:</p>
  
  <pre><code>AND (a_city.id IS NOT NULL
  OR a_floor.id IS NOT NULL)
  GROUP BY imobil.id
  HAVING COUNT(*) = 2
  </code></pre>
  
  <p>You you want to group your results and then count them, '= 2' means how many arguments have been passed, this will be counted in PHP. Thanks or your help.
  If your having trouble you might want to read this <a href="https://stackoverflow.com/questions/301793/mysql-using-count-in-the-where-clause">question</a> </p>