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.
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
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
select p.ProductId,p.ProductName
,(select top 1 inv.Details from Inventory inv where (p.ProductId = inv.ProductId))Details
from Products p
or
select * from
(
select p.ProductId,p.ProductName,inv.Details
,ROW_NUMBER()over(partition by p.productid order by p.productid)rn from Products p
inner join Inventory inv on(p.ProductId = inv.ProductId)
)t4
where rn=1
with cte as
(select p.ProductId,p.ProductName,inv.Details,
RoW_Number() OVER(Partition by p.ProductId,p.ProductName,inv.Details
Order by p.ProductId,p.ProductName,inv.Details) rn
from Products p
inner join Inventory inv on(p.ProductId = inv.ProductId))
select ProductId,ProductName,Details from cte where rn = 1
join?? As you can usesubqueryfor it