CODEWITHSUNDEEP
mysqlsql
Self-taught
Self-taught

Reputation: 15

how to substract between two tables by comparing them( according to a condition ) in sql?

I have two tables like this

table1

  ------------------------------------
date   | name    | charge | coupon| 
------------------------------------
10/01  | Alex    | 500    | 100   | 
11/01  | Max     | 1000   |  200  |  
13/01  |         |        |       |

table2 

  ------------------------------------
date   | name    | error  | refund | 
------------------------------------
10/01  | Alex    | crash  |        | 
|      |         |        |

I want to calculate refund to table2 from table1, if theres a error in table2. Consider date and name are unique.

I tried somthing like this, but it's not correct at all. I am a beginner to SQL

SELECT date, name, charge - coupon as Refund IF( error=True FROM table2 )
FROM table1

please help how to solve this ...

Upvotes: 1

Views: 25

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Hmmm . . . I think you want a join:

select t2.*, (t1.charge - t1.coupon) as refund
from table2 t2 join
     table1 t1
     on t1.name = t2.name and t1.date = t2.date
where t2.error is not null;

If you want all rows, with the refund conditionally on them:

select t2.*,
       (case when t2.error is not null then t1.charge - t1.coupon end) as refund
from table2 t2 join
     table1 t1
     on t1.name = t2.name and t1.date = t2.date;

Upvotes: 1

Related Questions