SQL Junction Query - How To Get Exact Match

Question

I would like the answer to this question to be DBMS agnostic, but if it is relevant I am using Access SQL.

Please keep note that this is a simplified version of what I am trying to do.

Now, consider I have the following three tables.

My main fruits table(tblFruits):

╔═════════╦═══════════╦
║ fruitID ║ fruitName ║ 
╠═════════╬═══════════╬
║ 1       ║ Apple     ║ 
║ 2       ║ Orange    ║ 
║ 3       ║ Grapefruit║ 
╚═════════╩═══════════╩

A junction table to link many tags to 1 fruit(tblFruitTagJunc):

╔════════════════╦═════════╦═════════════╗
║ fruitTagJuncID ║ fruitID ║ tagID       ║
╠════════════════╬═════════╬═════════════╣
║ 1              ║ 1       ║ 1           ║
║ 2              ║ 1       ║ 2           ║
║ 3              ║ 1       ║ 4           ║
║ 4              ║ 1       ║ 5           ║
║ 5              ║ 2       ║ 3           ║
║ 6              ║ 3       ║ 3           ║
║ 7              ║ 3       ║ 6           ║
╚════════════════╩═════════╩═════════════╝    

And finally a tag table to tag my fruits(tblTag):

 ╔═════════╦═══════════╗
 ║ tagID   ║  tag      ║
 ╠═════════╬═══════════╣
 ║ 1       ║ Tasty     ║
 ║ 2       ║ Red       ║
 ║ 3       ║ Orange    ║
 ║ 4       ║ Shiny     ║
 ║ 5       ║ Delicious ║
 ║ 6       ║ Awful     ║
 ╚═════════╩═══════════╝

_{Thanks to This Blog Post for letting me be lazy)}

This essentially says that:

1. Apples are (Red, Shiny, Tasty, Delicious)
2. Oranges are (Orange)
3. Grapefruits are (Orange, Awful)

Now say that I want to select those fruits that have the tag 'Orange' and no others. With the data presented, that would be only the one with fruitName = 'Orange'. I am currently doing this:

SELECT F.fruitName 
FROM tblFruits F
INNER JOIN tblFruitTagJunc AS FTJ on F.fruitID = FTJ.fruitID
INNER JOIN tbltag as T ON FTJ.tagID = T.tagID
WHERE T.tag in('Orange') 
GROUP BY F.fruitName
HAVING count(T.tag) = 1

This would return both Orange AND Grapfruit in the result, but I only wanted Orange.

The reason I am doing the SQL statement this way is that different types of fruits may have the same name but different tags OR different fruits may have all but one of the same tags.

EDIT:

SQLFiddle as requested.

Answer 1

You are on the right track, but you need conditional aggregation in the having clause rather than a where clause. When you use where, you never see the other tags.

So:

SELECT F.fruitName 
FROM tblFruits as F INNER JOIN
     tblFruitTagJunc AS FTJ
     on F.fruitID = FTJ.fruitID INNER JOIN
     tbltag as T
     ON FTJ.tagID = T.tagID
GROUP BY F.fruitName
HAVING SUM(iif(t.tag in ('Orange'), 1, 0) > 0 AND
       COUNT(t.tag) = 1;

Note that the "right" way to express conditionality is using CASE rather than IIF(). Also Access usually requires lots of ugly parentheses around joins, which I am also leaving out.