Aneeez Aneeez - 1 year ago 77
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

WHEN bill_type = 3 THEN b.payable_amount + w.balance
END) AS 'amount'

FROM bills b
LEFT JOIN ip_patients ip on = b.ip_id
LEFT JOIN advance w on = (SELECT FROM advance x WHERE x.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
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
table is not added to the sum.

Answer Source

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download