MySQL Forums
Forum List  »  Newbie

Join Statement with math operation
Posted by: stanley sioteco
Date: August 05, 2010 03:18AM

I have tried different queries to no avail.

Any help will be greatly appreciated.

i have four tables

cl_glist
cl_room_charges
cl_approval
cout_det

DROP TABLE IF EXISTS `lohas`.`cl_glist`;
CREATE TABLE `lohas`.`cl_glist` (
`cl_no` smallint(6) NOT NULL AUTO_INCREMENT,
`folio_no` varchar(15) NOT NULL DEFAULT '0',
`cindate` date DEFAULT NULL,
`coutdate` date DEFAULT NULL,
`nights` mediumint(8) DEFAULT NULL,
`lname` varchar(100) DEFAULT NULL,
`fname` varchar(100) DEFAULT NULL,
`nat` varchar(20) DEFAULT NULL,
`contact` varchar(45) DEFAULT NULL,
`rtype` varchar(20) DEFAULT NULL,
`rno` smallint(5) DEFAULT NULL,
`rate` decimal(10,2) DEFAULT NULL,
`sc` tinyint(1) DEFAULT NULL,
`remarks` varchar(400) DEFAULT NULL,
`p_details` varchar(100) DEFAULT NULL,
`p_disc` smallint(6) DEFAULT NULL,
`a_disc` varchar(30) DEFAULT NULL,
`group_ref_id` varchar(45) DEFAULT NULL,
`pcash` tinyint(1) DEFAULT NULL,
`pcc` tinyint(1) DEFAULT NULL,
`pcl` tinyint(1) DEFAULT NULL,
`pothers` tinyint(1) DEFAULT NULL,
`cbox` varchar(5) DEFAULT NULL,
`pax` smallint(2) DEFAULT NULL,
`sc_temp` decimal(10,2) DEFAULT NULL,
`source` varchar(45) DEFAULT NULL,
`com_rate` varchar(20) DEFAULT NULL,
`accnt_no` smallint(6) DEFAULT NULL,
PRIMARY KEY (`cl_no`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `lohas`.`cl_room_charges`;
CREATE TABLE `lohas`.`cl_room_charges` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`date` date DEFAULT NULL,
`item` varchar(45) DEFAULT NULL,
`qty` smallint(6) DEFAULT NULL,
`amount` decimal(10,2) DEFAULT NULL,
`disc` decimal(10,2) DEFAULT NULL,
`sc` decimal(10,2) DEFAULT NULL,
`net` decimal(10,2) DEFAULT NULL,
`outlet` varchar(45) DEFAULT NULL,
`remarks` varchar(200) DEFAULT NULL,
`rno` varchar(10) DEFAULT NULL,
`group_ref_id` varchar(10) DEFAULT NULL,
`type` varchar(20) DEFAULT NULL,
`code` varchar(50) DEFAULT NULL,
`time` varchar(10) DEFAULT NULL,
`cashier` varchar(30) DEFAULT NULL,
`os_no` varchar(10) DEFAULT NULL,
`lorder` smallint(3) DEFAULT NULL,
`folio_no` varchar(30) DEFAULT NULL,
`kind` varchar(3) DEFAULT NULL,
`rc_temp` varchar(20) DEFAULT NULL,
`tipno` varchar(20) DEFAULT NULL,
`post_date` date DEFAULT NULL,
`checkbox` varchar(1) DEFAULT 'q',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=93417 DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `lohas`.`cl_approval`;
CREATE TABLE `lohas`.`cl_approval` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`folio` varchar(45) DEFAULT NULL,
`approvedby` varchar(100) DEFAULT NULL,
`date` date DEFAULT NULL,
`time` time DEFAULT NULL,
`loc` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `lohas`.`cout_det`;
CREATE TABLE `lohas`.`cout_det` (
`folio` varchar(45) NOT NULL,
`date` date DEFAULT NULL,
`time` time DEFAULT NULL,
`loc` varchar(200) DEFAULT NULL,
`staff` varchar(200) DEFAULT NULL,
PRIMARY KEY (`folio`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

it's my first time to use join statements, in which i need help.
basically, the needed output will be like this:

Accnt No, Last name and First Name concatenated, Date Checked-in to Date Checked-out, room no with room type, Balance of Account, date of last payment, who approved this city ledger, staff who checked-out this guest.

i have this query:
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') - (select sum(amount) from cl_room_charges where kind = '1'), 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


I only have a problem getting the Balance of Account to display since i can get this data by subracting the cl_room_charges.amount paid which has a '1' cl_room_charges.kind with cl_room_charges.amount with '2' or '6' cl_room_charges.kind

Options: ReplyQuote


Subject
Written By
Posted
Join Statement with math operation
August 05, 2010 03:18AM


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.