Subquery , get latest price

Probably a simple question, but I cant get my mind to the logics here.. :banghead:

I have lots of products with changing prices. I need to get a list of all products with the latest price per product. I've done this in access with nested queries, but never in SQL before. I need a subquery (i think), and have been playing around for some time now.



These are the tables involved:



tblProd | product table, produkt is the productname

tblKurs | price table, NAV is the pricem and kursdato is the price date.



This is sort of what i want;



tblProd.produkt SisteKurs

"product 1" 10

"product 2" 8

"product 3" 7

"product 4" 15



This is what I have so far, but this only returns the latest price for product1 (not the rest of the products).

Quote:

SELECT dbo.tblProd.Produkt, dbo.tblKurs.NAV AS Sistekurs, dbo.tblKurs.Kursdato FROM dbo.tblProd INNER JOIN dbo.tblKurs ON dbo.tblProd.prodID = dbo.tblKurs.prodID WHERE (dbo.tblKurs.Kursdato = (SELECT MAX(Kursdato) AS xkursdato FROM dbo.tblKurs AS tblKurs_x))

Subquery , get latest price