Return distinct on a table

Question

I have a 2 tables, one is a list of products and another is a products image table linked by the productID.

I have wrote the following SQL query however i am not sure how to only to return a distinct on each product as some products have multiple images against them.

SELECT  p.ProductID, p.Title, pi.FileName
    FROM [Ordering].[Products] p
    JOIN [Ordering].[ProductImages] pi ON p.ProductID =pi.FK_ProductID 
    WHERE p.Title like '%' + @Title +'%'
    ORDER BY p.Title ASC

--Result

ProductID   Title                 FileName
5           test                  5.jpg
2           test product          4.jpg
2           test product          3.jpg
2           test product          2.jpg
2           test product          1.jpg

As in the result above what i am trying to do is only return 2 rows, i.e test and testproduct.

Thanks.

Answer 1

You could return a distinct list of products with a comma separated list of file paths:

SELECT  
    p.ProductID, 
    p.Title, 
    img.ImageFilePaths
FROM [Products] p
JOIN (
SELECT DISTINCT
    t1.ProductID,
    ImageFilePaths = SUBSTRING((SELECT ( ', ' + t2.FileName )
                       FROM [ProductImages] t2
                       WHERE t1.ProductID = t2.ProductID                                                    
                       FOR XML PATH( '' )
            ), 3, 1000 )
            FROM [ProductImages]  t1
        GROUP BY t1.ProductID) img
ON img.ProductID = p.ProductID  

And using @mellamokb fiddle:

http://www.sqlfiddle.com/#!3/a3103/8

Answer 2

You can group by product and grab the MAX image (or some other method depending on which image you want to represent the product):

    SELECT p.ProductID, p.Title, MAX(pi.FileName) as FileName
      FROM [Ordering].[Products] p
INNER JOIN [Ordering].[ProductImages] pi ON p.ProductID =pi.FK_ProductID 
     WHERE p.Title like '%' + @Title +'%'
  GROUP BY p.ProductID, p.Title
  ORDER BY p.Title ASC

Demo: http://www.sqlfiddle.com/#!3/6f498/2