CODEWITHSUNDEEP
sqlselectjoin
SoBiT
SoBiT

Reputation: 408

SQL select from Table2 if it has a value, else from Table1

I have two tables with the following data:

Table1:

PRODUCT | PRICE | NEWPRICE

Table2:

PRODUCT | PRICE | NEWPRICE

Table1 is complete. Table2 has been created to update Table1. Table1 contains data from last year, while Table2 is up to date. But Table2 only contains PRODUCT's which have a NEWPRICE this year.

Now what I try to do is:

SELECT 
    Table1.PRODUCT, Table1.PRICE, 
    **(IF Table2.NEWPRICE -> select this, ELSE Table1.NEWPRICE)**

Is there any way to do this? Maybe with some kind of JOIN?

PS: I know that the tables were not created wisely, but I have to work with them now, because I have to continue the work of someone else >.<

Upvotes: 1

Views: 1915

Answers (1)

jpw
jpw

Reputation: 44881

I believe this is what you want:

SELECT 
  Table1.PRODUCT, Table1.PRICE, COALESCE(Table2.NEWPRICE, Table1.NEWPRICE) AS "New Price"
FROM Table1 LEFT JOIN Table2 ON Table1.Product = Table2.Product

The left join will take all rows from Table1 and the matching rows from Table2 and if there is no matching row from Table2 then the Table2.NEWPRICE will be NULL and the COALESCE will get the Table1.NEWPRICE value.

This assumes that all products exists in Table1. If that isn't the case you can build a source of all products with a union of the two tables and use that as the left side for two left joins like this:

select 
  all_products.product, 
  Table1.PRICE, 
  coalesce(Table2.Newprice, Table1.Newprice) AS "New Price"
from (select product from table1 union all select product from table2) all_products
left join Table1 on all_products.product = Table1.product
left join Table2 on all_products.product = Table2.product

Upvotes: 2

Related Questions