I have this test SqlFiddle: http://sqlfiddle.com/#!18/05fce/1
The idea is that for each product (table webspiderproduct) it will return the lowest price from each shopId from the table webspiderproductprice.
So the returned data should look like this:
x
ProductId: 1, ProductName: "sample product1"
shopId: 1, lowestPrice: 100
shopId: 2, lowestPrice: 199
ProductId: 2, ProductName: "sample product 2"
shopId: 1, lowestPrice: 99
shopId: 2, lowestPrice: 119
shopId: 3, lowestPrice: 19
I am using SQL Server.
In pseudocode should I write a
select *
from webspiderproduct
and then do a foreach through each row using linq and then add the child rows from webspiderproductprice or should I do a join query ?
Advertisement
Answer
Use the following code:
select t1.ProductId
,t1.productname
,t2.shopId
,t2.price as lowestPrice
from
webspiderproduct t1
join
(select *,ROW_NUMBER() over(partition by webspiderproductid,shopid
order by price asc) as rn from webspiderproductprice) t2
on t1.productid=t2.webspiderproductid
where rn=1
order by ProductId
Output: