sql remove duplicate record while using join

Question

I have a following table structure which can't be change.

I'm trying to join these table and want to avoid duplicate records as well.

 select p.ProductId,p.ProductName,inv.Details from Products p
inner join Inventory inv on(p.ProductId = inv.ProductId)

here is SqlFiddle.

Answer 1

From sqlserver 2008+ you can use cross apply.

With cross apply you can make a join inside the subselect as demonstrated here. With top 1 you get maximum 1 row from table Inventory. It will also be possible to add an 'order by' statement to the subselect. However that seems out of scope for your question.

select p.ProductId,p.ProductName,x.Details 
from Products p
cross apply
(SELECT top 1 inv.Details FROM Inventory inv WHERE p.ProductId = inv.ProductId) x

Answer 2

You can use row_number function to remove duplicates

   WITH New_Inventory AS
    (
        SELECT Productid, Details,
        ROW_NUMBER() OVER (Partition by Productid ORDER BY details) AS RowNumber
        FROM Products
    ) 
    select p.ProductId,p.ProductName,inv.Details from Products p
    inner join New_Inventory inv on(p.ProductId = inv.ProductId)
    where RowNumber = 1