INNER JOIN Returns Too Many Results

Question

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

instead of just two records, which these names are "home" and "car". How can I fix it?

Thanks.

Answer 1

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') 

Answer 2

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

Answer 3

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

Answer 4

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'