CODEWITHSUNDEEP
sqlsql-serverjoin
DoIt
DoIt

Reputation: 3438

Return best values of two SQL tables along with the values from table 1 that has no match in table2

I am working on a SQL query in SQL Server 2012, the Query should compare two columns of type double in two table and return the bigger value of two tables which can be done using a SQL Join, but I wanted something beyond that it should also return the rows which has no match in second table and also it should place a null value in one field if one of the values is bigger and other smaller.

For Example I have two tables T1 and T2 with following rows

Table T1

Product Price Tax
Pen     10    2.5
Pencil   5    1.25
Paper   15    3.2
Board   25    5.26
Water    2    NULL

Table T2

 Product Price  Tax   
 Pencil   8     2.5
 Paper    12    4.2
 Board    NULL  4.26
 Water    1     2.5
 Book     10    5

Upon running the SQL query on the above two tables the result should be like

Results

 Product Price Tax
 Pen     10    2.5
 Pencil   8    2.5
 Paper   15    4.2
 Board   25    5.26
 Water    2    2.5

Based on the values from above, we can see that Second table has no match for product Pen which is the first row of the first table so the resultant table should have the entire row but this should not be the case with the Product Book which is the last row of second table and has no match in the first table so that record should be discarded and also NULL values should be replaced by value in other table in T1 or T2.

Upvotes: 0

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You need to do a left outer join and then compare the values afterwards:

select t1.product,
       (case when t1.price >= t2.price then t1.price
             when t2.price > t1.price then t2.price 
             else coalesce(t1.price, t2.price)
        end) as price,
       (case when t1.tax >= t2.tax then t1.tax
             when t2.tax > t1.tax then t2.tax 
             else coalesce(t1.tax, t2.tax)
        end) as tax
       coalesce(t1.tax,t2.tax) as tax
from table1 t1 left join
     table2 t2
     on t1.product = t2.product;

Upvotes: 2

Related Questions