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.
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.