How to write query for this database?

SELECT price.code,price.price_date,price.price
FROM price
WHERE price.code=price.code
code Price_date Price
100 01/Jun/2006 2500
200 01/Jun/2006 3000
100 02/Jun/2006 2300
200 02/Jun/2006 2500
100 03/Jun/2006 5000
200 03/Jun/2006 4000
100 04/Jun/2006 1000
200 04/Jun/2006 2000
Actual i want to display like
Code, Price_date (currentdate) , Pricecurrent, (price_Date)Oldate-1, oldprice ( As per oldate)
like yesterdays price, and date
Thanks