title: multiple table select using many where clauses, is there a better way than self-join?
body: <p>I've got a bunch of tables that I'm joining using a unique item id. The majority of the where clause conditions will be built programatically from a user sumbitted form (search box) and multiple conditions will often be tested against the same table, in this case item tags.</p>
  
  <p>My experience with SQL is minimal, but I understand the basics. I want to find the ids of active (status=1) items that have been tagged with a tag of a certain type, with the values "cats" and "kittens". Tags are stored as (id, product_id, tag_type_id, value), with id being the only column requiring a unique value. My first attempt was;</p>
  
  <pre><code>   select 
        distinct p2c.product_id 
     from '.TABLE_PRODUCT_TO_CATEGORY.' p2c
        inner join '.TABLE_PRODUCT.' p on p2c.product_id = p.id 
        inner join '.TABLE_PRODUCT_TAG.' pt on p.id = pt.product_id
        inner join '.TABLE_TAG_TYPE.' tt on pt.tag_type_id = tt.id
     where 
        tt.id = '.PRODUCT_TAG_TYPE_FREE_TAG.'
        and p.status = 1
        and lower(pt.value) = "cats"
        and lower(pt.value) = "kittens"
  </code></pre>
  
  <p>but that returned nothing. I realised that the final AND condition was the problem, so tried using a self-join instead;</p>
  
  <pre><code>   select 
        distinct p2c.product_id 
     from '.TABLE_PRODUCT_TO_CATEGORY.' p2c
        inner join '.TABLE_PRODUCT.' p on p2c.product_id = p.id 
        inner join '.TABLE_PRODUCT_TAG.' pt on p.id = pt.product_id
        inner join '.TABLE_PRODUCT_TAG.' pt2 on p.id = pt2.product_id
        inner join '.TABLE_TAG_TYPE.' tt on pt.tag_type_id = tt.id
     where 
        tt.id = '.PRODUCT_TAG_TYPE_FREE_TAG.'
        and p.status = 1
        and lower(pt.value) = "cats"
        and lower(pt2.value) = "kittens"
  </code></pre>
  
  <p>Now everything works as expected and the result set is correct. So what do I want to know? To re-iterate, the results I'm after are the ids of active (status = 1) items that have been tagged with a tag of a certain type, with the values "cats" AND "kittens"...</p>
  
  <ol>
  <li>Are self-joins the best way of achieving these results?  </li>
  <li>This query has the potential to be huge (I've omitted a category condition, of which there may be ~300), so does this self-join approach scale well? If not, is there an alternative? </li>
  <li>Will the self-join approach be the best way forward (assuming there is an alternative) if I allow users to specify complex tag searches? ie "cats" and ("kittens" or "dogs") not "parrots".</li>
  </ol>