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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With