Try this
SELECT cl_glist.cl_no
, concat(cl_glist.lname, ', ', cl_glist.fname)
, concat(date_format(cl_glist.cindate, '%b %e, %y'), ' - '
, date_format(cl_glist.coutdate,'%b %e, %y'))
, concat(cl_glist.rno, ' - ', cl_glist.rtype)
, ((SELECT sum(amount)
FROM cl_room_charges
WHERE (kind = '2' or kind = '6') and cl_glist.folio_no = cl_room_charges.folio_no
- COALESCE((SELECT sum(amount)
FROM cl_room_charges
WHERE kind = '1' and cl_glist.folio_no = cl_room_charges.folio_no)),0) account_balance
, max(cl_room_charges.post_date)
, cl_approval.approvedby
, cout_det.staff
FROM cl_glist
LEFT
OUTER
JOIN cl_room_charges
ON cl_glist.folio_no = cl_room_charges.folio_no
LEFT
OUTER
JOIN cl_approval
ON cl_glist.folio_no = cl_approval.folio
LEFT
OUTER
JOIN cout_det
ON cl_glist.folio_no = cout_det.folio
GROUP BY cl_glist.folio_no
ORDER BY cl_glist.folio_no ASC;
Good luck,
Barry.