title: MySQL keyword search query to match items with ALL keywords?
body: <p>I have a table structure which is basically:</p>
  
  <pre><code>Items
  ID: INT(10)
  Title: CHAR(255)
  
  ItemsWords
  ID: INT(10)
  ItemID: INT(10)
  Word: CHAR(50)
  </code></pre>
  
  <p>For each 'Item' record, there are multiple records in the 'ItemsWords' table.</p>
  
  <p>I can find all items using this which contain a single word like this:</p>
  
  <pre><code>SELECT * FROM Items i, ItemsWords w WHERE w.ItemID=i.ID AND w.Word='WORD_TO_FIND'
  </code></pre>
  
  <p>I can find all items using this which contain any of a number of words like this:</p>
  
  <pre><code>SELECT * FROM Items i, ItemsWords w 
  WHERE w.ItemID=i.ID 
  AND (w.Word='WORD_TO_FIND' OR w.Word='ANOTHER_WORD_TO_FIND')
  </code></pre>
  
  <p>But I also want to be able to use the same table structure to find only the 'Items' which contain ALL of any number of search words.</p>
  
  <p>I know I could use FullText by the <code>Items</code> table has over 100,000 records and the <code>ItemWords</code> table many time more than that, so have set up the structure this way to make these searches very quick and with minimum server impact. The one word or ANY word searches above are indeed super speedy.</p>
  
  <p>Thank you!</p>