title: Tagging: SQL query to return tagged and untagged items
body: <p>I'm building a database that will incorporate tagging and have gone with the 'toxi' schema where I have 3 tables:</p>
  
  <pre><code>Items: Item, ItemID, more fields
  Tags: TagName, TagID, more fields
  TagMap: TagID, ItemID
  </code></pre>
  
  <p>I'm trying to build a query that will list all of items and their tags, where many items may have no tags. I can't find a query that can do both.</p>
  
  <p>The following query gets me items with tags by name, but will only pull items that <em>have</em> tags.</p>
  
  <pre><code>SELECT Items.Item, Tags.Tag
  FROM Items INNER JOIN 
  (Tags INNER JOIN TagMap ON Tags.ID = TagMap.TagId)
  ON Items.ID = TagMap.ItemId;
  </code></pre>
  
  <p>The following query gets me all of the items and their TagIDs if they've been tagged, but I can't seem to get to the next step of converting those TagIDs to TagNames in a single query.</p>
  
  <pre><code>SELECT [Items].Item, [TagMap].TagId
  FROM Items LEFT JOIN TagMap ON [Items].ID=TagMap.ItemId;
  </code></pre>
  
  <p><a href="http://msdn.microsoft.com/en-us/library/office/bb243855%28v=office.12%29.aspx" rel="nofollow">MSDN</a> says "OUTER JOINs can be nested inside INNER JOINs in a multi-table join, but INNER JOINs cannot be nested inside OUTER JOINs," but I can't find any way of nesting the two joins that does not throw an error. As far as I can see, I need the LEFT JOIN to be on Items.</p>
  
  <p>I'm working in Access to build my proof on concept database because it's already installed on my machine. I will probably switch to SQL Server at some point. </p>