CODEWITHSUNDEEP
mysqljoin
skypanther
skypanther

Reputation: 995

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?

Upvotes: 9

Views: 9688

Answers (2)

The Scrum Meister
The Scrum Meister

Reputation: 30111

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.

Upvotes: 15

Tyler Eaves
Tyler Eaves

Reputation: 13131

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.

Upvotes: 5

Related Questions