SQL Outer Join Function

Question

I wrote this function before, but I can’t remember it, and it did not fall into version control. Now, more like sleep deprivation than anything else, I don't remember how to recover it.

Here is an idea. I have two tables: "regPrice" and "custPrice", with the common key "itemID". They both have a “price” column, and custPrice also has another “acct” key, so if the price exists in custPrice, it should return it. If there is no custPrice entry, it should return regPrice.

pseudo code:

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

Answer 2

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

Answer 3

COALESCE, NULL, CASE.