Connection fields in table 2 override the values ​​in table 1

Question

I have a product table that stores the "main" prices:

Products
===========.
identifier
partnum
description
price
installation time

Dealers can redefine the price of the list, set a different price, installation time, etc. I was thinking about storing the differences in the dealer settings in another table:

redefines
===========.
dealerID
partnum
price
installation time

When I request db for dealer prices, I need to join these tables. I need the values ​​in the override table to override the values ​​in the product table.

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

As written, this, of course, will give an error. The fact is that I need a price from the redefinition table if it exists instead of the price in the product table (the same for instaltime). I could use different field names and move the logic to the PHP level. But SQL should be able to handle it, right?

Answer 1

Use LEFT JOIN together with IFNULL to check overrides first and discard if override does not exist.

 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 WHERE dealerID = 123 to the join predicate to get all products and overrides for a particular dealer.

Answer 2

Do this as a left join, and then use coalesce in each field with the first override column. Coalesce returns the first non-empty argument.

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