title: SQL to find articles with ALL of a set of tags
body: <p>Finding articles with ANY of a set of tags is a relatively simple join, and has been discussed already: <a href="https://stackoverflow.com/questions/900337/best-db-mysql-structure-articles-which-contain-favored-tags">Best DB (MySQL) structure: Articles which contain favored tags</a></p>
  
  <p>But what if I'm searching, and want to find articles with ALL of a set of tags?</p>
  
  <p>For specificity, assume the following tables:</p>
  
  <pre><code>CREATE TABLE `articles` (
     `id` INT NOT NULL
  );
  
  CREATE TABLE `applied_tags` (
     `tag_id` INT NOT NULL,
     `article_id` INT NOT NULL
  );
  
  CREATE TABLE `search_tags` (
     `search_id` INT NOT NULL,
     `tag_id` TAG NOT NULL
  );
  </code></pre>
  
  <p>I came up with this, which I think might work, but it's massive, ugly, and kinda unclear, so I figure there must be a better way:</p>
  
  <pre><code>Select articles.id from articles
  where
   ( select count(*) from applied_tags
     where applied_tags.article_id == articles.id
       and applied_tags.tag_id in (select search_tags.tag_id from search_tags where search_tags.search_id == &lt;input&gt;) 
       ==
   (select count(*) from search_tags where search_tags.search_id == &lt;input&gt;)
  </code></pre>
  
  <p>(Essentially, count if the number of relevant tags is the expected value.)</p>