user3197661 user3197661 - 2 months ago 8
MySQL Question

How to calculate from 2 tables in SQL

How to create query for join Table A and B with condition A.salary * B.rate

Table A
---------------------
customerID salary
A1 100
A2 200
A3 300

Table B
---------------------
customerID rate
A1 2
A2 3
A3 4


My expect result.

---------------------
customerID salary
A1 200
A2 600
A3 1200

Answer
SELECT t1.customerID,
       COALESCE(t1.salary * t2.rate, 'NA') AS salary
FROM TableA t1
LEFT JOIN TableB t2
    ON t1.customerID = t2.customerID

I used COALESCE when computing the effective salaries in case either the salary amount or rate be NULL for any customer.

Demo here:

SQLFiddle