Join - fields in table 2 override those in table 1

I have a products table that stores "master" pricing:

products

========== .

id

partnum

description

price

installtime

Dealers can override the list price, setting开发者_开发技巧 a different price, install time, etc. My thought was to store dealer-specific differences in another table:

overrides

========== .

dealerID

partnum

price

installtime

When I query the db for a dealer's prices, I need to join these tables. I need the values in the overrides table to override those in the products table.

SELECT partnum, price, installtime FROM products  
JOIN overrides ON products.partnum = overrides.partnum  
WHERE dealerID = 123

As written, this would give an error of course. The point is I need the price from the overrides table if one exists instead of the price in the products table (same for instaltime). I could use different field names and move the logic to the PHP layer. But SQL should be able to handle it, right?

Use a LEFT JOIN together with IFNULL to check first the overrides, and fallback if no override exists.

SELECT p.partnum, 
    IFNULL(d.price, p.price) AS price,
    IFNULL(d.installtime, p.installtime) AS installtime
FROM products p 
  LEFT JOIN overrides d ON d.dealerID = 123
      AND p.partnum = d.partnum  

Note: I moved the WHERE dealerID = 123 to the join predicate, to retrieve all products, and the overrides for a specific dealer.

Do it as a left join, and then use coalesce on each field, with the override column first. Coalesce returns the first non-null argument.

select coalesce(overrides.partnum, products.partnum) ... etc.