Easy select ????

I' have problems doing a query into our product register.

I have two tables

VVARPF contains ProductInformation
VVVARE = ProductNumber
VVTEK = ProductDescription

VVPRPF contains PriceChanges
VPVARE = ProductNumber
VPDATE = ChangedDate
VPPRICE = ProductPrice

I need to lookup the price on the product at the current time. The VVPRPF register can contain prospective pricechanges used for future campaigns etc. That means I can't select the largest date.

It assumes the price which last changed before current date is the right price.


select product.VVVARE, price.VPPRICE
from VVARPF product, VVPRPF price
where product.ProductNumber = price.ProductNumber
and price.VPDATE =
(select max(VPDATE)
from VVPRPF
where VPDATE < getdate()
and VVPRPF.VVVARE = product.VVVARE)

Thanks, it works !!!!!!