title: Category filter IN and IN for ecommerce
body: <p>I am writing an ecommerce script but have stumbled upon a problem:
  MySQL can't use <code>WHERE cat_id = 'software' AND attr_id IN (1,2) AND attr_id IN (3,4)</code> as it consider ambiguous.</p>
  
  <p>So, to explain the DB logic it is like this :</p>
  
  <p><code>attrTitle</code> is the title of the <code>attr</code>. Example: "<em>Operating System</em>", "<em>License Type</em>"</p>
  
  <p><code>attr</code> holds the attribute related to the <code>attrTitle</code>. Each <code>product</code> can have multiple <code>attr</code> and every <code>attr</code> is linked by an FK to an <code>attrTitle</code>. Example of <code>attr</code> : "<em>Windows</em>", "<em>Linux</em>", "<em>Freeware</em>"</p>
  
  <p><code>product</code> is... well... products. Example: "<em>Microsoft Office</em>"</p>
  
  <p>and <code>category</code> are categories... Each <code>product</code> is linked to only one category... Example: "<em>Office Suites</em>"</p>
  
  <p>So, i want the user to be able to filter the products in the "<em>Office Suites</em>" category. For that, he have a menu saying: </p>
  
  <pre><code>[FILTERS]
  
  *Operating System
  - Windows
  - Linux
  - Mac
  
  *Language
  - English
  - Español
  </code></pre>
  
  <p>So, suppose an user is looking for an Office suite that works in Windows <em>AND</em> is in English or Español.</p>
  
  <p>The query became something like this :</p>
  
  <pre><code>SELECT *
  FROM prd_product p0_ 
  INNER JOIN cat_category c2_ 
      ON p0_.cat_id = c2_.cat_id 
  LEFT JOIN prd_attr p4_ 
      ON p0_.prd_id = p4_.prd_id 
  LEFT JOIN attr_attributeValue e3_ 
      ON e3_.attr_id = p4_.attr_id 
  WHERE c2_.cat_friendlyUrl = 'software' 
      AND e3_.attr_id IN (4) 
      AND e3_.attr_id IN (10,11)
  GROUP BY p0_.prd_id 
  </code></pre>
  
  <p>But, as i said, it becomes ambiguous for MySQL as the espval_id is getting an WHERE IN clause two times, and then it returns an empty result. Could anyone give me a tip on how to solve this ?</p>
  
  <p>And yes, i tested and there is a product with attr 4 and attr 10</p>
  
  <p><strong>EDIT:</strong> Sql Fiddle : <a href="http://sqlfiddle.com/#!2/be3198/1" rel="nofollow">http://sqlfiddle.com/#!2/be3198/1</a></p>