title: SQL one-to-many LEFT OUTER JOIN: perform an AND query
body: <p>This should be common enough and I'm looking for the "best" way to perform this in one SQL query (MySQL).</p>
  
  <p>I have three tables, an <code>items</code> table, a <code>linker</code> table and a <code>tags</code> table. Items can be tagged multiple times, so the linker is a simple foreign key linker table:</p>
  
  <pre><code>items   | linker  | tags  
  --------+---------+-------
  item_id | item_id | tag_id
  ...     | tag_id  | name  
  --------+---------+-------
  </code></pre>
  
  <p>I can search <code>items</code> for single tags easily, how would I go to search items that have 2 or more specific tags?</p>
  
  <pre><code>SELECT *, `tags`.`name`
  FROM `items`
  LEFT OUTER JOIN `linker` USING (`item_id`)
  LEFT OUTER JOIN `tags` USING (`tag_id`)
  WHERE `tags`.`name` = "tag-a"
  </code></pre>
  
  <p>How does a sane person perform search for 2 or more tags, an item must have ALL the tags, i.e. an <code>AND</code> query?</p>
  
  <hr>
  
  <p>Edit: What I have so far is the following, which works and doesn't seem to be slow, but looks crazy:</p>
  
  <pre><code>SELECT `items`.* FROM `items`
  LEFT OUTER JOIN `linker` USING (`item_id`)
  LEFT OUTER JOIN `tags` USING (`tag_id`)
  WHERE (
          `item_id` IN (SELECT item_id FROM linker LEFT JOIN tags USING (tag_id) WHERE name = "tag-a")
      AND `item_id` IN (SELECT item_id FROM linker LEFT JOIN tags USING (tag_id) WHERE name = "tag-b")
      AND `item_id` IN (SELECT item_id FROM linker LEFT JOIN tags USING (tag_id) WHERE name = "tag-c")
      AND `item_stuff` = "whatever"
  )
  </code></pre>