Complex Query Question - PLEASE HELP!

[Guest]

I have a table called price that is a table with the following columns:

VendorID
Region
Product
Price

This table has many records. Each Region is either a 0, 1, or a 2. Region 0
is a special
region that means "default". Thus if a record does not exist for a specific
product in
region 1 or 2, we want to use the default region.

we want to produce a "price list" for a given region (e.g. 1) which should
give us all
the region specific product records (Where Region=1), and any additional
"default" product
records that exist in the table that didn't have a record for Region=1.

I tried a query with a where clause of "Where Region = 1 or 0" but if there
are product
records for both region 1 and region 0, I only want to retrieve the record
for
region 1. The example I used return 2 records for the product.

 

[Guest]

SELECT P.VendorID, P.Product, P.Region, P.Price
FROM Price as P,
(SELECT VendorID,
Product,
Max(Price.Region) AS MaxOfRegion
FROM Price
GROUP BY Price.VendorID, Price.Product) As M
WHERE P.VendorID = M.VendorID
AND P.Product = M.Product
AND P.Region = M.MaxOfRegion ;