SQL Join with CASE and WHERE

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

Answer 2

First thing I notice is that you have a predicate condition on table e, which is on the "outer" side of an outer join. This will immediately turn the join into an Inner join, as the rows that would ordinarilly have been produced where there is a record on the inner side but no record on the outer side, would have all nulls in the columns from the outer table by the time the where clause predicate (WHERE e.ProductID = 48) is executed.

You need to put this predicate in the join conditions instead.
like this:

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

Next, as Joels answer recommended, for a simple expression, although a case would work, you don't need a case statement, Coalesce will suffice:

 SELECT r.ID AS ShippingRateID, r.Name, 
     Coalesce(e.rate, defaultrate) as Rate,
     e.*, r.*
 FROM shipping r 
    LEFT JOIN  shippingexceptions e 
        ON r.ID = e.ShippingRateID
             And e.ProductID = 48

Answer 3

SELECT  COALESCE(
        (
        SELECT  TOP 1 cost
        FROM    shippingexceptions se
        WHERE   se.ShippingRateID = r.id
                AND se.ProductID = 48
        ), DefaultPrice
        ) AS price
FROM    shipping r

, or in SQL Server 2005+:

SELECT  COALESCE(cost, DefaultPrice) AS price
FROM    shipping r
OUTER APPLY
        (
        SELECT  TOP 1 cost
        FROM    shippingexceptions
        WHERE   ShippingRateID = r.id
                AND ProductID = 48
        ) se

Answer 4

IF you are on MSSQL, COALESCE function would help. It returns first not-null.

It is, select COALESCE(shippingexceptions.Rate, shipping.Rate )

Do the same thing with case.. when :

Select Case when shippingexceptions.Rate is not null THEN shippingexceptions.Rate
ELSE shipping.Rate

Hope this helps..