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 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 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)