CODEWITHSUNDEEP
sqlmysqlstored-proceduresjoin
Drew
Drew

Reputation: 15418

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.

Upvotes: 3

Views: 650

Answers (3)

Gordon Bell
Gordon Bell

Reputation: 13633

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

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562641

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

Upvotes: 6

atfergs
atfergs

Reputation: 1684

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

Upvotes: 1

Related Questions