SQL Where Clause with Joined Table - OR works, but AND Doesn't
Assume I have 2 tables defined as follows:
Items
-----
Id (Primary Key)
ItemName
ItemsTags
-----
ItemId (Primary Key)
TagName (Primary Key)
and filled with the following data:
Items
-----
1, Item1
2, Item2
3, Item3
ItemsTags
--------
1, Tag1
1, Tag2
1, Tag3
2, Tag3
3, Tag4
3, Tag5
Assuming I want to search of items that have EITHER Tag1 or Tag3 assigned,
the following query works:
SELECT DISTINCT Items.Id
FROM Items INNER JOIN ItemsTags
ON Items.Id = ItemsTags.ItemId
WHERE ItemsTags.TagName = 'Tag1' OR ItemsTags.TagName = 'Tag3'
resulting in 1 and 3 being returned.
However, how do I modify that query to give me items that have BOTH Tag1
and Tag3? The following query does not work, obviously, because for any
given row, TagName cannot be two different values at once.
SELECT DISTINCT Items.Id
FROM Items INNER JOIN ItemsTags
ON Items.Id = ItemsTags.ItemId
WHERE ItemsTags.TagName = 'Tag1' AND ItemsTags.TagName = 'Tag3'
What is the correct query? In my example, I would want to get only item 1
back.
No comments:
Post a Comment