Help with a query design

[Dan]

I have three tables: Customers, Products and CustomerProducts. For
simplicity, the Customers table has two fields: CustID and CustName. The
Products table has three fields: ProdID, ProdName and ProdPrice. The
CustomerProducts table has three fields: CustID, ProdID and CustProdPrice. In
this last table, the CustID and ProdID are the primary key.

For a given CustID and ProdID, I want to retrieve the CustProdPrice field
from the CustomerProducts table if a record exists for this CustID/ProdID
pair. Otherwise, I want to retrieve the ProdPrice field from the Products
table.

Can this be done with a query? Or do I have to do it with VB code?

Thanks for your help!

 

[Allen Browne]

This sort of thing should do it:

SELECT Customers.CustID,
Products.ProdID,
Nz((SELECT CustProdPrice FROM CustomerProducts
WHERE (CustomerProducts.CustID = Customers.CustID) AND
(CustomerProducts.ProdID = Products.ProdID)), Products.ProdPrice)
AS Price
FROM Customers, Products;

The key concepts here are:
a) Cartesian product: there is no join between Customers and Products, which
generates a record for every possible combination of the two.

b) Subquery: looks up the customer's special price for the product in
CustomerProducts.

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html