INNER JOIN SQL query includes duplicates

Question

I have an online store and am trying to display five similar products on the product page. There is a many-to-many relationship between products and tags which I'm trying to query (join table) using INNER JOIN. Here is the SQL query I have thus far:

SELECT TOP(5) *
FROM [Store_ProductTags] AS [s]
INNER JOIN [Store_Products] AS [s0] ON [s].[ProductId] = [s0].[Id]
ORDER BY CASE WHEN TagId IN (1, 17) THEN 0 ELSE 1 END;

It does what I want (which is getting five products with the same tags as the product that is being displayed), however, includes duplicates...

This is the result I'm getting (notice the two records with the ProductId of 1):

ProductId   TagId       Name
10          17          Harlots - The Woman You Saw... (CD)
1           1           Unholy - Blood of the Medusa (CD)
44          1           Unholy - Blood of the Medusa (LP)
1           2           Unholy - Blood of the Medusa (CD)
2           2           Lye By Mistake - Arrangements for Fulminating Vective (CD)

I'd like to exclude duplicates. So far I've tried DISTINCT and GROUP BY, as other response in different questions showed, to no avail. The error I receive when I add DISTINCT after SELECT is...

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Any suggestions?

Answer 1

You can group by product and sort the results by the number of matched tags:

SELECT TOP(5) p.Id, p.Name
FROM Store_Products AS p INNER JOIN Store_ProductTags AS s 
ON s.ProductId = p.Id
GROUP BY p.Id, p.Name
ORDER BY SUM(CASE WHEN s.TagId IN (1, 17) THEN 1 ELSE 0 END) DESC;