At the first look...
Striked content is redundant!
select cstdetails.loandate, cstdetails.cstname, cstdetails.loanamt,
cstduebill.cstname,cstduebill.loanslno, cstduebill.loanamt,
cstduebill.billdate, cstduebill.totdueamt
from cstdetails where cstdetails.loandate between '2020-05-20' and '2020-05-20'
LEFT JOIN cstduebill ON cstdetails.cstname = cstduebill.cstname WHERE rectno IN(
SELECT max(rectno) FROM cstduebill WHERE billdate between '2020-05-20' and '2020-05-20'
GROUP BY loanslno)
AND billdate between '2020-05-20' and '2020-05-20' ORDER BY loanslno
BTW: Please, use table aliases:
SQL Aliases[
^]
SELECT cd.loandate, cd.cstname, cd.loanamt,
cb.cstname, cb.loanslno, cb.loanamt, cb.billdate, cb.totdueamt
FROM cstdetails cd
LEFT JOIN cstduebill cb ON cd.cstname = cb.cstname
WHERE rectno IN(
SELECT max(rectno)
FROM cstduebill
WHERE billdate between '2020-05-20' and '2020-05-20'
GROUP BY loanslno)
AND billdate between '2020-05-20' and '2020-05-20'
AND cd.loandate between '2020-05-20' and '2020-05-20'
ORDER BY loanslno