INNER JOIN Returns Too Many Results
I have the following SQL:
SELECT *
FROM [Database].dbo.[TagsPerItem]
INNER JOIN [Database].dbo.[Tag] ON [Tag].Id = [TagsPerItem].TagId
WHERE [Tag].Name IN ('home', 'car')
and it returns:
Id TagId ItemId ItemTable Id Name SiteId
------------------------------------------
1 1 1 Content 1 home 1
2 1 2 Content 1 home 1
3 1 3 Content 1 home 1
4 2 4 Content 2 car 1
5 2 5 Content 2 car 1
6 2 12 Content 2 car 1
instea开发者_StackOverflowd of just two records, which these names are "home" and "car". How can I fix it?
Thanks.
It's because you're telling the query to return every column, so SQL will return every matching row. So you'll need to change the query to only return the columns you need and add a DISTINCT clause (i.e. SELECT DISTINCT
).
If you provide an example of the output you want, we might be able to provide a more useful answer...
There are two alternatives - using JOINs:
SELECT *
FROM [Database].dbo.[TagsPerItem] tpi
JOIN [Database].dbo.[Tag] t_home ON t_home.id = tpi.tagid
AND t_home.name = 'home'
JOIN [Database].dbo.[Tag] t_car ON t_car.id = tpi.tagid
AND t_car.name = 'car'
...or HAVING COUNT(DISTINCT t.name) = 2
:
SELECT --column list - fill with specific appropriate columns
FROM [Database].dbo.[TagsPerItem] tpi
JOIN [Database].dbo.[Tag] t ON t.id = tpi.tagid
AND t.name IN ('home', 'car')
GROUP BY --column list - match columns declarations with the SELECT list without aggregates used on them
HAVING COUNT(DISTINCT t.name) = 2
- The
COUNT(DISTINCT
is necessary, otherwise two relations of "car" would be a false positive (assuming possible in the data model) - Not all databases support
COUNT(DISTINCT...)
; while tedious to construct, JOINing to the same table multiple times is a safer method
SELECT DISTINCT Tag.Id, Tag.Name
FROM [Database].dbo.[TagsPerItem]
INNER JOIN [Database].dbo.[Tag] ON [Tag].Id = [TagsPerItem].TagId
WHERE [Tag].Name IN ('home', 'car')
SELECT *
FROM [Database].dbo.[TagsPerItem] tpi_car
JOIN [Database].dbo.[TagsPerItem] tpi_home on tpi_home.ItemId = tpi_car.ItemId AND tpi_home. ItemTable = tpi_car. ItemTable
JOIN [Database].dbo.[Tag] t_home ON t_home.id = tpi_home.tagid
AND t_home.name = 'home'
JOIN [Database].dbo.[Tag] t_car ON t_car.id = tpi_car.tagid
AND t_car.name = 'car'
精彩评论