The Newbie The Newbie - 2 months ago 5
MySQL Question

select id, table1.column1 + table2.column2 as total from table1 join table2. what if table2.column2 has no value?

help. T_T

table 1

+---------------+---------+-------------------+
| allowances_id | desc_id | column1 |
+---------------+---------+-------------------+
| 1 | 1 | 64055.35594866848 |
| 2 | 4 | 55627.97197247496 |
| 3 | 6 | 55627.97197247496 |
| 4 | 7 | 55627.97197247496 |
| 5 | 8 | 55627.97197247496 |
| 6 | 9 | 55627.97197247496 |
| 7 | 2 | 50293.50333209634 |
+---------------+---------+-------------------+


table 2

+---------+-------+
| desc_id | total |
+---------+-------+
| 1 | 18150 |
| 4 | 18150 |
| 6 | 18150 |
| 7 | 18150 |
| 8 | 18150 |
| 9 | 18150 |
+---------+-------+


i want table1.column1 + table2.total

table1 (desc_id has a value of 2 in table2 desc_id has no value of 2 so basically the result should be column1 + 0.00

Answer
SELECT t1.column1 + COALESCE(t2.column2, 0)
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.id = t2.id
Comments