sql server misc: I'm looking for the most optimized query for this scenario:

I have a product table

ID Name
A Test A
B Test B
C Test C

and a price table

ID Date Price Test
A 01-01-2006 10 Price 1 A
A 01-05-2006 12 Price 2 A
B 01-05-2006 20 Price 1 B
C 01-01-2006 30 Price 1 C
C 01-05-2006 35 Price 2 C
C 01-09-2006 10 Price 3 C

(The date column has dd-mm-yyyy format and is the date when the price
starts)

Now I have to do a query to retrieve all products and his price for a
given date.

For example, for the date 15-05-2006 the query has to return:

ID Name Date Price Test
A Test A 01-05-2006 12 Price 2 A
B Test B 01-05-2006 20 Price 1 B
C Test C 01-05-2006 35 Price 2 C

I have done it with a los of sub queries but I'm sure there is a
better way to do it.

A lot of thanks,

Alex B.

I asked this very same question of a colleague just yesterday and we
came to the conclusion that there might be a method using the GROUP BY
[WITH ROLLUP] or something to that - we didnt' actually lookup the
syntax - along with BLACOLUMN IS NULL <bla de bla>.

We both decided in the end that sub-queries for complex set-logic /
math is likely more maintainable. So unless you have serious
performance issues, leave what works in place.

Cheers
http://rickhathaway.blogspot.com/