title: MySQL: many to many relationship with multiple AND condition
body: <p>I have a many-to-many relationship in my MySQL-database with three tables like this:</p>
  
  <p>TABLE item,
  TABLE relation (storing only ids of items and tags),
  TABLE tag</p>
  
  <p>Each item can have multiple tags also tags can be related to multiple items, e.g. the item "shoe" can have the tags "sport" and "leather" while the tag "sport" could be related to items "shoe" and "shirt".</p>
  
  <p>What I need to do now is select all items that have one or many tags combined by an AND-condition, so e.g. I'd like to find all items that have the tag "sport" AND the tag "leather" related.</p>
  
  <p>Is it possible to retrieve the requested data with an AND-condition using just one query or will I need to build subqueries (or something like that)? </p>
  
  <p>The alternative is to get all items with the tags combined with OR like this:</p>
  
  <pre><code>SELECT *
  FROM item
  LEFT JOIN relation
      ON item.id = item_id
      LEFT JOIN tag
      ON tag.id = tag_id
  WHERE (tag = 'sport' OR tag = 'leather')
  GROUP BY item.id;
  </code></pre>
  
  <p>and then filtering out the ones that don't have all necessary tags, but I'd rather have the database do the work instead of fetching unnecessary items and then iterating.</p>