sql - select from two tables and conditionally collapse one column -

i have list of products

upc | name     | price | qty ---------------------------- 1   | apple    |  1.00 |  3 2   | peach    |  2.00 |  7 3   | melon    |  1.75 |  2 

and saleproducts

upc | price ------------ 2   | 1.90 

i want select products sale price saleproducts (if product on sale). came with:

select t1.upc, t1.name, min(t1.price) 'price', t1.qty (   select p.upc, p.name, p.price, p.qty   products p     union   select sp.upc, null, sp.price, null   saleproducts sp   ) t1 group t1.upc; 

output:

upc | name     | price | qty ---------------------------- 1   | apple    |  1.00 |  3 2   | peach    |  1.90 |  7 3   | melon    |  1.75 |  2 

can suggest more elegant way accomplish this? im aware of similar question goal grab whichever price lower, coalesce wouldn't work.

the restriction is, must use vanilla sql, no stored procs or if's.

try instead using case:

select p.upc, p.name,   case when sp.price not null     case when p.price > sp.price       sp.price       else p.price     end     else p.price   end price, p.qty products p left join saleproducts sp on p.upc = sp.upc; 

it prefer use price saleproducts when it's available. if there not sale price product instead use price products.

edit - i've updated answer gets lowest price. fwiw can't imagine why you'd bother having sale price higher than list price.