CODEWITHSUNDEEP
sqlsql-server
DoIt
DoIt

Reputation: 3438

Unable Compare the values of two columns of two tables and returning the best row if it is in the first table

I am working on a query which compares two tables and returns one or zero rows for each type of product. It returns zero if after comparing the values of two tables and if second row has at least one better row than the first table and it returns one row if the first table has the best row.

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

Table T1

Product Price Tax
Pen     10    2.25
Pen      5    1.25
Pen     15    1.5
Board   25    5.26
Board    2    NULL
Water    5    10

Table T2

 Product Price  Tax   
 Pen      8     2.5
 Pen      12    4.2
 Board    NULL  4.26
  Book     10    5

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

Product Price Tax
Pen     15    NULL
Board   25    5.26
Water    5    10

The above results can be explained as Price of Pen in T1 is 15 which is the highest of two tables but tax for pen is highest in T2 hence we return NULL for tax and 15 for Price Similarly Board has both price and tax in T1 which are highest so 25 and 5.26 are returned and Water has no entry in T2 so highest values in T1 are returned and for Book there are no values returned as T1 has no entry for Book

I am using the below query but they aren't yielding expected results

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

Upvotes: 0

Views: 91

Answers (3)

Tab Alleman
Tab Alleman

Reputation: 31785

You need to do grouping and aggregations. To be safe, I would use derived tables:

select t1.product,
       (case when t1.price >= COALESCE(t2.price,0) then t1.price
             else NULL
        end) as price,
       (case when t1.tax >= COALESCE(t2.tax,0) then t1.tax
             else NULL
        end) as tax
from (SELECT product, MAX(price) as price, MAX(tax) as tax FROM table1 GROUP BY product) t1 left join
     (SELECT product, MAX(price) as price, MAX(tax) as tax FROM table2 GROUP BY product) t2
     on t1.product = t2.product;

Upvotes: 1

nathan_jr
nathan_jr

Reputation: 9282

Heres another way, using row_number() to enumerate the prices for us in two derived tables:

declare @T1 table (Product varchar(10), Price int, Tax decimal(10,2))
declare @T2 table (Product varchar(10), Price int, Tax decimal(10,2))

insert into @T1
       select 'Pen',     10,    2.25 union all
       select 'Pen',      5,    1.25 union all
       select 'Pen',     15,    1.5 union all
       select 'Board',  25,    5.26 union all
       select 'Board',   2,    NULL union all
       select 'Water',   5,    10;

insert into @T2
       select 'Pen',      8,     2.5 union all
       select 'Pen',      12,    4.2 union all
       select 'Board',    NULL,  4.26


select one.Product,
       [Price] = case when one.Price > isnull(two.Price, 0) then one.Price else two.Price end,
       [Tax] = case when one.Tax > isnull(two.Tax, 0.0) then one.Tax else null end
from   (
              select Product, Price, Tax, [r]= row_number() over(partition by Product order by Price desc)
              from @t1
       ) one
left
join   (
              select Product, Price, Tax, [r]= row_number() over(partition by Product order by Price desc)
              from @t2
       ) two
       on one.Product = two.Product and two.r=1
where  one.r = 1

Upvotes: 0

mungea05
mungea05

Reputation: 110

I believe what you are looking for is

SELECT t1.Product, 
    CASE 
        WHEN MAX(t1.Price) >= MAX(t2.Price) THEN MAX(t1.Price)
        WHEN MAX(t2.Price) > MAX(t1.Price)  THEN NULL
        ELSE COALESCE(MAX(t1.Price), MAX(t2.Price)) 
    END AS Price,
    CASE 
        WHEN MAX(t1.Price) >= MAX(t2.Price) AND MAX(t1.Tax) >= MAX(t2.Tax) THEN MAX(t1.Tax)
        WHEN MAX(t1.Price) >= MAX(t2.Price) AND MAX(t1.Tax) < MAX(t2.Tax)  THEN NULL
        ELSE COALESCE(MAX(t1.Tax), MAX(t2.Tax)) 
    END AS Tax
   FROM #Temp t1
   LEFT JOIN #Temp1 t2
   ON t1.Product = t2.Product 
   GROUP BY t1.Product, t2.Product

Upvotes: 1

Related Questions