Outer join based on range?

Can you do an outer join based on a range (><) rather than an equality?

I need to display a list of ordered products and prices (if available) for a date range.
Products cost different amounts during different time periods.

My price reference table is keyed on product and date range.

example:	product-1; from-3/1/97; to-3/30/97; price-$100
		product-1; from-4/1/97; to-4/31/97; price-$125
		product-2; from-3/1/97; to-3/31/97; price-$450


		... (thousands of records here)


My order table lists products and dates ordered.
example:	product 1; date-3/15/97
		product 2; date-5/15/97


		... (note: there will be substantially more than 2 records here)

My query needs to return all products in the order, and prices for the products with matching records in the price reference table:

example:	product 1; date-3/15/97; price-$100
		product 2; date-5/15/97; price-(null)
		...

The only other ideas I can think of involve finding all the products WITH prices, using this as a NOT IN query to find products WITHOUT prices, then UNIONing these results with another instance of the first query (all products WITH prices)
OR
Looping through a list of all products and doing a separate query for each product.

Since this is a query that will populate a dynamic Web Server page, performance is an important issue. The tables accessed are already on slow, foreign databases (via DBLINKS), and I believe the last two approaches may be speed-prohibitive.

If you have any ideas, they would be greatly appreciated.

Thanks,
Damien Jones (damien.jones_at_attws.com) Received on Tue Mar 25 1997 - 00:00:00 CST