Alex Rivera | Logout

Join - fields in table 2 override those in table 1

Asked 2011-03-07T16:22:05.877
9

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?

mysql join
Edit
Report

1 Answer

15

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.

answered 2011-03-07T16:25:06.903

Your Answer