Aneeez Aneeez - 2 months ago 6
SQL Question

Columns are ignored when no matching row found in LEFT JOIN while using CASE WHEN

I want to fetch a collection report of bills paid for a hospital. There's a table where advance amount paid by patients are recoreded. Not all patients pay advance.

This is the query I use now

"SELECT SUM(
CASE
WHEN bill_type = 3 THEN b.payable_amount + w.balance
ELSE 0
END) AS 'amount'

FROM bills b
LEFT JOIN ip_patients ip on ip.id = b.ip_id
LEFT JOIN advance w on w.id = (SELECT x.id FROM advance x WHERE x.ip_id = ip.id ORDER BY x.created_at DESC LIMIT 1)
WHERE b.doctor_id = '$d->id' AND CAST(b.created_at AS DATE) >= '$date1' AND CAST(b.created_at AS DATE) <= '$date2' AND b.is_cancelled = 0 AND b.is_deleted = 0"


If a patient didn't pay the advance, no row is inserted in
advance
table. What happens with the above query is that, when there's no row for a patient in advance table, his payment is totally ignored (ie, the value in
bills
table is not added to the sum.

Answer

If you add a null to a not null the result is null. for example

MariaDB [sandbox]> set @a = null;
Query OK, 0 rows affected (0.00 sec)

MariaDB [sandbox]> set @b = 100;
Query OK, 0 rows affected (0.00 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> select @a+@b amt;
+------+
| amt  |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

And you can use ifnull to deal with this

set @a = null;
set @b = 100;

select ifnull(@a,0) + ifnull(@b,0) amt;

+-----+
| amt |
+-----+
| 100 |
+-----+
1 row in set (0.00 sec)