0

Good afternoon,

I have a question: I want to retrieve a list that will give me the cheapest price + package description for each product_id.

I have a query, but this returns all the packages with the cheapest price.

this is the query I created:

select p.Product_ID, vp.verpakking_omschrijving, lowprice
from VerpakkingProduct vp
inner join Product p
on vp.Product_ID=p.Product_ID
inner join(
select p.product_id, min(vw.prijs) as lowprice from Product P
inner join VerpakkingProduct vp
on p.Product_ID=vp.Product_ID
inner join VerpakkingWinkel vw
on vp.Verpakking_ID=vw.Verpakking_ID
group by p.Product_ID) as prijslijst on prijslijst.Product_ID=p.Product_ID
inner join VerpakkingWinkel vw 
on vw.Verpakking_ID=vp.Verpakking_ID and vw.prijs=prijslijst.lowprice

The query to retrieve the lowest price for each product_id is correct. Additionally I want to add the package description (Verpakking_omschrijving) for the lowest price of each product_id. Can someone help me?

Thank you.

Afi
  • 13
  • 3
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – SMor Jun 18 '20 at 14:40
  • The problem is that some of the packages have the same price. Therefore both packages are in the list. How can I make sure I only get one of the packages? – Afi Jun 18 '20 at 15:16
  • How would you choose which of the packages to display and which one not to? – Robert Sievers Jun 18 '20 at 18:39

1 Answers1

0

Use below query. This will return only one product.

select MIN(p.product_id), min(vw.prijs) as lowprice 
from Product P
inner join VerpakkingProduct vp on p.Product_ID=vp.Product_ID
inner join VerpakkingWinkel vw on vp.Verpakking_ID=vw.Verpakking_ID
group by p.Product_ID
Wowo Ot
  • 1,362
  • 13
  • 21
SMK
  • 11
  • 1