MySQL Forums
Forum List  »  Analytics, Reporting, BI

mysql query with multiple transactions
Posted by: noie lendio
Date: May 03, 2012 10:30PM

-- ----------------------------
-- Table structure for driver
-- ----------------------------
DROP TABLE IF EXISTS `driver`;
CREATE TABLE `driver` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fullname` varchar(100) COLLATE utf8_unicode_ci NOT NULL
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Table structure for taxi
-- ----------------------------
DROP TABLE IF EXISTS `taxi`;
CREATE TABLE `taxi` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`unit` varchar(5) NOT NULL
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for debts
-- ----------------------------
DROP TABLE IF EXISTS `debts`;
CREATE TABLE `debts` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`data` float(10,2) NOT NULL DEFAULT '0.00',
`driver` bigint(20) NOT NULL,
`dateadded` datetime NOT NULL
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for dispatch
-- ----------------------------
DROP TABLE IF EXISTS `dispatch`;
CREATE TABLE `dispatch` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`driver` int(11) NOT NULL,
`taxi` int(11) NOT NULL,
`dispatchdate` date DEFAULT NULL,
`rate` float DEFAULT '0'
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1790 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for rpayment
-- ----------------------------
DROP TABLE IF EXISTS `rpayment`;
CREATE TABLE `rpayment` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`dateadded` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=88 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for rpayment_detail
-- ----------------------------
DROP TABLE IF EXISTS `rpayment_detail`;
CREATE TABLE `rpayment_detail` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`rpayment` bigint(20) NOT NULL,
`dispatch` bigint(20) NOT NULL,
`amount` float DEFAULT '0'
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=latin1;

INSERT INTO `driver` VALUES ('1', 'DRIVER1');
INSERT INTO `driver` VALUES ('2', 'DRIVER2');
INSERT INTO `driver` VALUES ('3', 'DRIVER3');

INSERT INTO `taxi` VALUES ('1', 'UNIT1');
INSERT INTO `taxi` VALUES ('2', 'UNIT2');
INSERT INTO `taxi` VALUES ('3', 'UNIT3');

INSERT INTO `debts` VALUES ('1','100.00', '1', '2012-04-01 16:07:15');
INSERT INTO `debts` VALUES ('2','200.00', '1', '2012-04-01 16:25:56');
INSERT INTO `debts` VALUES ('3','300.00', '3', '2012-04-01 16:34:42');
INSERT INTO `debts` VALUES ('4','400.00', '2', '2012-04-02 00:11:10');
INSERT INTO `debts` VALUES ('5','200.00', '1', '2012-04-02 00:57:58');
INSERT INTO `debts` VALUES ('6','500.00', '3', '2012-04-02 10:25:39');
INSERT INTO `debts` VALUES ('7','100.00', '2', '2012-04-02 11:15:25');

INSERT INTO `dispatch` VALUES ('1', '1', '1', '2012-04-01', '1000');
INSERT INTO `dispatch` VALUES ('2', '2', '2', '2012-04-01', '1000');
INSERT INTO `dispatch` VALUES ('3', '3', '3', '2012-04-01', '1000');
INSERT INTO `dispatch` VALUES ('4', '1', '1', '2012-04-02', '1000');
INSERT INTO `dispatch` VALUES ('5', '2', '2', '2012-04-02', '1000');
INSERT INTO `dispatch` VALUES ('6', '3', '3', '2012-04-02', '1000');

INSERT INTO `rpayment` VALUES ('1', '2012-04-30 20:11:16');
INSERT INTO `rpayment` VALUES ('2', '2012-05-03 03:25:31');

INSERT INTO `rpayment_detail` VALUES ('1', '1', '1', '1000');
INSERT INTO `rpayment_detail` VALUES ('2', '1', '4', '0',);
INSERT INTO `rpayment_detail` VALUES ('3', '2', '2', '0');
INSERT INTO `rpayment_detail` VALUES ('4', '2', '5', '500')

I want to have a result like the following:


UNIT DRIVER RPAYMENT_TOTAL DEBTS
---------------------------------------------
UNIT1 DRIVER1 1000 500
UNIT2 DRIVER2 500 500
UNIT3 DRIVER3 0 800

Please help, thanks,

noister

Options: ReplyQuote


Subject
Written By
Posted
mysql query with multiple transactions
May 03, 2012 10:30PM


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.