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

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 = ?;