SQL Join with CASE and WHERE

Question

I have three tables - one for shipping rates, one for products and one for exceptions to shipping rate for particular products. Shipping is charged as follows: Each product has a shipping price, but this price can be overridden by an exception. If no exception exists for a product, the default rate is used for the shipping rate selected by the user. alt text http://mi6.nu/sqljoin.png I'm trying to join these tables so that if an exception exists, that price is selected, otherwise, the default price is selected but I'm having problems with the join. I need to query by product ID, and I have (line 2 is for debugging)

SELECT r.ID AS ShippingRateID, r.Name, 
e.*, r.*
FROM shipping r LEFT JOIN shippingexceptions e ON r.ID = e.ShippingRateID
WHERE e.ProductID = 48

And I need returned:

1   Uk and Northern Ireland 1
2   EU Eire...      10
3   US and Canada       2.16
4   Rest of world       2.44

So if an exception exists, the exception price is used, otherwise the default price is used. I'm planning to use a CASE statement, but I need to return the data first.

Answer 1

Why not use a coalesce

SELECT r.ID AS ShippingRateID, r.Name, coalesce(e.cost, r.defaultprice)
FROM shipping r LEFT JOIN shippingexceptions e ON r.ID = e.ShippingRateID
WHERE e.ProductID = 48

This way, if e.rate is null, r.rate is used.

You also should put your e.ProductId on the join so it doesn't force you to select only products with exceptions

SELECT r.ID AS ShippingRateID, r.Name, coalesce(e.cost, r.defaultprice)
FROM shipping r LEFT JOIN shippingexceptions e ON r.ID = e.ShippingRateID and e.ProductID = 48