SQL Outer Join Function

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.

From stackoverflow

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

  Joel Coehoorn : Sounds like sql server- he asked for mysql

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

  Drew : Looks good. To make it a stored procedure it would just be: CREATE PROCEDURE pricing (I_itemID INT, I_acct INT) SELECT COALESCE(c.price, r.price) AS price FROM regPrice r LEFT OUTER JOIN custPrice c ON (r.itemID = c.itemID AND c.acct = I_acct) WHERE r.itemID = I_itemID; Is this correct?

  Bill Karwin : Yes that procedure works fine.

• 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
  

  Drew : Is there a performance difference between a CASE or a COALESCE?

  Gordon Bell : No, there shouldn't be.