SQL Help - Criteria Based On 2 Fields 1 Table

[Cascot]

Sorry about the generic subject. Hard to sum this one up.

I am trying to design a query that displays multiple customers
products. Each product has a sell price, however in some cases a
separate table (PriceException) will have an entry that indicates a
non-standard price for a certain customer / product dombination. In
such instances I want to show the non-standard price. All of that is
easy, EXCEPT the PriceException table contains two controlling fields
- CustomerID and ProductID.


Given that there will be many customers with no entries in the
PriceException table, as well as customers who have entries but not
for all products, how do I achieve the SQL equivalent of:


Display Product.Price Unless (PriceException.CustomerID =
Customer.CustomerID AND PriceException.ProductID =
Product.ProductID),
in which case display PriceException.Price


Any help would be much appreciated.

 

[John Spencer]

More details about the tables would help to devise a solution.

For instance how is product related to customer? Do you have an many to many
relationship with a conjunction table?

A customer has many products
A product has many customers

If you do have the conjunction table then you could use an outer join to the
PriceException table to get the exception price. And use that if it existed,
otherwise use the standard price.

The query would look something like the following.

SELECT C.CustomerID, Nz(PE.Price,P.Price) as ThePrice
FROM ((Customer as C Inner Join ConjunctionTable as CT
ON C.CustomerID = CT.CustomerID) INNER JOIN Product as P
ON CT.ProductID = P.ProductID) LEFT JOIN PriceException as PE
ON CT.CustomerID = PE.CustomerID and CT.ProductID = PE.ProductID

Of course, all that is base on speculation about your table structure.
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County