mysql query with multiple transactions

Question

i have this sample tables structure and records:

-- ----------------------------
-- 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;

-- ----------------------------
-- Records 
-- ----------------------------

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');

and I want to view result like the following:

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

I have this for now...

SELECT    taxi.unit, driver.fullname, SUM(rpayment_detail.amount) AS rpayment_total, 
          SUM(debts.`data`) AS total_debts 
FROM      driver 
LEFT JOIN debts ON (driver.id = debts.driver) 
LEFT JOIN dispatch ON (driver.id = dispatch.driver) 
LEFT JOIN rpayment_detail ON (dispatch.id = rpayment_detail.dispatch) 
LEFT JOIN rpayment ON (rpayment_detail.rpayment = rpayment.id) 
LEFT JOIN taxi ON (dispatch.taxi = taxi.id) 
GROUP BY  driver.id 
ORDER BY  taxi.unit asc, driver.fullname asc

result is...

UNIT     DRIVER     RPAYMENT_TOTAL     TOTAL_DEBTS
--------------------------------------------------
UNIT1    DRIVER1    3000               1000.00
UNIT2    DRIVER2    1000               1000.00
UNIT3    DRIVER3    null               1600.00

Sorry, please check post #1. I just add the 2 table structures. — noister
I answered your question as asked and you accepted it. You then added new variables, CHANGIng the question, and unaccepted my answer - changing the question doesn't make my answer unacceptable. — GDP

Bohemian Bohemian · Accepted Answer · 2012-05-04T19:09:02

It can't be done with the table structure you have.

Your problem is that debts needs a foreign key column to dispatch instead of driver.

Without this, every debt will be joined in for every dispatch, effectively multiplying the number of debt by the number of dispatches for the driver, which is what you're seeing.

mysql query with multiple transactions

1 Answers