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.

show and explain the database structure! Why do you expect only two rows from that JOIN?? — marc_s
– marc_s, Commented Mar 27, 2010 at 22:19
What result exactly are you expecting? Are you trying to get all items which are tagged both home and car? There aren't any item's tagged with both in your example. — Michał Piaskowski
– Michał Piaskowski, Commented Mar 27, 2010 at 22:23

Callie J · Accepted Answer · 2010-03-27 22:20:47Z

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

It's better if you use Group By instead of DISTINCT, it's faster.

OMG Ponies · Accepted Answer · 2010-03-27 22:41:34Z

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

Hueso Azul · Accepted Answer · 2010-03-27 22:19:50Z

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

Michał Piaskowski · Accepted Answer · 2010-03-28 12:12:21Z

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'

Collectives™ on Stack Overflow

INNER JOIN Returns Too Many Results

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Related