user3197661 - 1 year ago 58

MySQL Question

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 Source

```
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:**