SQL Help - Criteria Based on 1 Table 2 Fields

  • Thread starter Thread starter Cascot
  • Start date Start date
C

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.
 
Ok generically speaking, show all Customer ID, Product ID and non-standard
price - where non-standard price not equal to null?
 
Ok generically speaking, show all Customer ID, Product ID and non-standard
price - where non-standard price not equal to null?
Click to expand...

Almost.

Show all Customer ID, Product ID and standard price info, and where
non-standard
price not equal to null show non-standard price.
 
Back
Top