SQL Outer Join Function

Question

I've written this function before but I can't seem to remember it and it didn't get into version control. Now, more to do with sleep deprivation than anything else, I can't remember how to rebuild it.

Here's the idea. I have two tables, "regPrice" and "custPrice", with shared key "itemID." They both have a "price" column and custPrice also has another key "acct" such that if a price exists in custPrice, it should return that one. If there isn't a custPrice entry, it should return the regPrice.

pseudoCode:

if(select custPrice where acct = passedAcct and itemID = passedItemID) {
   return custPrice;
else 
   return regPrice;

Any help would be appreciated.

Answer 1

Look into the COALESCE function, unless you're not using NULL, in which case you'll want to use a CASE.

Answer 2

SELECT COALESCE(c.price, r.price) AS price
FROM regPrice r LEFT OUTER JOIN custPrice c
 ON (r.itemID = c.itemID AND c.acct = ?)
WHERE r.itemID = ?;

Answer 3

select r.itemID, r.Acct,
case when c.price is null then r.price else c.price end as price
from regPrice r
left outer join custPrice c
on r.itemID = c.itemID
and r.Acct = @passedAcct
where r.itemID = @passedItemID