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)
- (SELECT COALESCE(sum(amount),0)
FROM cl_room_charges
WHERE kind = '1' and cl_glist.folio_no = cl_room_charges.folio_no) 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;
It's really hard to test this without your tables and data so I can build it to reproduce what you want.
If this doesn't go, do
SHOW CREATE TABLE tablename
for all the tables in your query, and provide a small but representative set of data, preferebaly as INSERT statements so I can build your database.
Good luck,
Barry.