CODEWITHSUNDEEP
mysql
noister
noister

Reputation: 9

mysql query with multiple transactions

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

Upvotes: 0

Views: 738

Answers (1)

Bohemian
Bohemian

Reputation: 425013

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.

Upvotes: 0

Related Questions