Tagging: SQL query to return tagged and untagged items

I'm building a database that will incorporate tagging and have gone with the 'toxi' schema where I have 3 tables:

Items: Item, ItemID, more fields
Tags: TagName, TagID, more fields
TagMap: TagID, ItemID

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.

The following query gets me items with tags by name, but will only pull items that have tags.

SELECT Items.Item, Tags.Tag
FROM Items INNER JOIN 
(Tags INNER JOIN TagMap ON Tags.ID = TagMap.TagId)
ON Items.ID = TagMap.ItemId;

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.

SELECT [Items].Item, [TagMap].TagId
FROM Items LEFT JOIN TagMap ON [Items].ID=TagMap.ItemId;

MSDN 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.

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.

Solution

You were close -- just needed to add 1 additional LEFT JOIN:

SELECT [Items].Task, [TagMap].TagId, Tags.TagName
FROM Items 
    LEFT JOIN TagMap ON [Items].ID=TagMap.TaskId
    LEFT JOIN Tags ON TagMap.TagID=Tags.TagID

Since you're using MS Access, you'll need to use parentheses around your joins -- something like this should be close:

SELECT [Items].Task, [TagMap].TagId, Tags.TagName
FROM (Items 
    LEFT JOIN TagMap ON [Items].ID=TagMap.TaskId)
    LEFT JOIN Tags ON TagMap.TagID=Tags.TagID