argh...date problems

Hi,

I have a quick SQL query that i'm having problems with. Basically i have a product table and a price history table which have a common product_code field. The price history table contains the history of price changes for all products, so there will be one or more rows in it for each product_code (each having an effective_date field which determines when the price becomes/became effective).

I need to query the price table for a list of products and return one row per product containing the product information and current price information for that product

My level of SQL is pretty basic and i can't figure out how to restrict the rows returned from the price table to just the current prices (i.e. the max effective_date before the current date)In Oracle:

select * from product_table p, product_hist h
where h.prod_cd = p.prod_cd
and h.eff_date = (
select MAX(eff_date) from product_hist d
where d.prod_cd = p.prod_cd);
;)|||cheers for that