Re: Join Statement with math operation

[stanley sioteco]

i got it sir, i just re-arranged coalesce.

thank you for pointing me to the right direction. :)

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