Re: Join Statement with math operation

[stanley sioteco]

thank you for trying sir, i added some more to your codes and made it work, partially..

i added the folio numbers to the subquery

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 sum(amount)
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;

but those accounts which has no payments (kind = '1') doesn't show up any value altogether instead of showing up all payables. i guess when mysql bumps into a null value, it produces a null result.

thanks sir