Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Join - fields in table 2 override those in table 1

Tags:

join

mysql

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?

like image 555
skypanther Avatar asked Mar 07 '11 16:03

skypanther


1 Answers

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.

like image 192
The Scrum Meister Avatar answered Nov 15 '22 20:11

The Scrum Meister