CODEWITHSUNDEEP
sqlsql-serversql-server-2008join
John
John

Reputation: 115

Take the best row from two table on a particular column

I have two tables with almost the same data from two companies with different prices.

I want to select the row from any one of the table which has low price.

How do I compare rows and take the best value from the two table

I have written the query as this but there is ambiguity in the price part.

select min(Price), Brand, a.Color 
from [table1] a, table2 b 
where a.BrandName = b.BrandName and a.Shape like b.Shape 
      and (a.color = b.color or a.Color is null)

I am getting ambiguity error in price. How do i Solve it.

Upvotes: 0

Views: 46

Answers (2)

Aritra Bhattacharya
Aritra Bhattacharya

Reputation: 780

Can you try this : 

 select 
    case when min(a.price)<min(b.price) then min(a.price) else min(b.price) end as     min_price, 
    Brand,a.Color from [table1] a, table2 b where a.BrandName=b.BrandName and a.Shape = b.Shape and a.color=b.color
group by Brand,a.color
    UNION 
    select 
    case when min(a.price)<min(b.price) then min(a.price) else min(b.price) end as     min_price, 
    Brand,a.Color from [table1] a, table2 b where a.BrandName=b.BrandName and a.Shape = b.Shape and  a.Color is null
group by Brand,a.color

Upvotes: 0

sagi
sagi

Reputation: 40481

You can do this with UNION if you wanna use MIN :

SELECT min(price),brand,max(color)
FROM (select price,brand,color FROM table1
      UNION
      select price,brand,'' FROM table2)
GROUP BY brand

Or alternatively you can use CASE EXPRESSION with a join: 

select CASE WHEN a.price > b.price then b.price else a.price end as min_price
       ,a.Brand
       ,a.Color
from table1 a
INNER JOIN  table2 b
ON a.BrandName=b.BrandName
WHERE and a.Shape like b.Shape and (a.color=b.color or  a.Color is null)

Upvotes: 1

Related Questions