Join Statement with math operation
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