MySQL Forums :: Newbie :: Join Statement with math operation


Advanced Search

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 stanley sioteco 08/05/2010 03:18AM
Re: Join Statement with math operation Barry Galbraith 08/05/2010 03:56AM
Re: Join Statement with math operation stanley sioteco 08/05/2010 05:21AM
Re: Join Statement with math operation Barry Galbraith 08/05/2010 05:34AM
Re: Join Statement with math operation stanley sioteco 08/05/2010 05:56AM
Re: Join Statement with math operation Barry Galbraith 08/05/2010 06:08AM
Re: Join Statement with math operation stanley sioteco 08/05/2010 07:11AM
Re: Join Statement with math operation laptop alias 08/24/2010 06:40AM
Re: Join Statement with math operation stanley sioteco 08/05/2010 07:56AM
Re: Join Statement with math operation Barry Galbraith 08/05/2010 08:25PM
Re: Join Statement with math operation stanley sioteco 08/06/2010 12:13AM
Re: Join Statement with math operation Barry Galbraith 08/06/2010 12:25AM
Re: Join Statement with math operation Rick James 08/07/2010 03:30PM
Re: Join Statement with math operation tan tan 08/22/2010 06:23AM
Re: Join Statement with math operation Rick James 08/22/2010 08:51AM
Re: Join Statement with math operation tan tan 08/22/2010 08:56AM
Re: Join Statement with math operation laptop alias 08/22/2010 09:18AM
Re: Join Statement with math operation Rick James 08/22/2010 12:41PM
Re: Join Statement with math operation laptop alias 08/22/2010 12:51PM
Re: Join Statement with math operation Rick James 08/22/2010 04:32PM


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.