MySQL Forums
Forum List  »  Newbie

Re: Join Statement with math operation
Posted by: Barry Galbraith
Date: August 05, 2010 06:08AM

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.

Options: ReplyQuote


Subject
Written By
Posted
Re: Join Statement with math operation
August 05, 2010 06:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.