I need the second SELECT to JOIN (GROUP?) items which have same user, color and piece.
And sum quantities (but this I can try to figure out myself, that joining is the problem)
Query now:
SELECT *
FROM (SELECT test1_loose.USER,
test1_loose.color,
test1_loose.piece,
test1_loose.quantity
FROM test1_loose
UNION
SELECT test2_boxes.own_user,
test3_boxinv.color,
test3_boxinv.piece,
(test3_boxinv.quantity * test2_boxes.own_quantity) AS quantity
FROM test2_boxes,
test3_boxinv
WHERE test2_boxes.own_item = test3_boxinv.item) AS ss
Result:
+------+-------+---------+----------+
| user | color | piece | quantity |
+------+-------+---------+----------+
| 9 | 99 | piece13 | 9100 |
| 9 | 66 | piece19 | 9200 |
| 9 | 99 | piece13 | 100 |
| 9 | 99 | piece14 | 200 |
| 9 | 22 | piece15 | 300 |
| 9 | 99 | piece13 | 2100 |
| 9 | 44 | piece16 | 2200 |
| 4 | 99 | piece33 | 12300 |
| 4 | 77 | piece13 | 12600 |
+------+-------+---------+----------+
But it should looke like this:
+------+-------+---------+----------+
| user | color | piece | quantity |
+------+-------+---------+----------+
| 9 | 99 | piece13 | 11300 |
| 9 | 66 | piece19 | 9200 |
| 9 | 99 | piece14 | 200 |
| 9 | 22 | piece15 | 300 |
| 9 | 44 | piece16 | 2200 |
| 4 | 99 | piece33 | 12300 |
| 4 | 77 | piece13 | 12600 |
+------+-------+---------+----------+
Tables:
CREATE TABLE `test1_loose` (
`id` int(11) NOT NULL auto_increment,
`piece` varchar(255) default NULL,
`user` int(11) default NULL,
`quantity` int(11) default NULL,
`color` int(11) default NULL,
`log` varchar(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
#
# Dumping data for table `test1_loose`
#
INSERT INTO `test1_loose` VALUES (1, 'piece13', 9, 9100, 99, NULL);
INSERT INTO `test1_loose` VALUES (2, 'piece19', 9, 9200, 66, NULL);
# --------------------------------------------------------
#
# Table structure for table `test2_boxes`
#
CREATE TABLE `test2_boxes` (
`id` int(11) NOT NULL auto_increment,
`own_user` int(11) default NULL,
`own_item` varchar(255) default NULL,
`own_quantity` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
#
# Dumping data for table `test2_boxes`
#
INSERT INTO `test2_boxes` VALUES (1, 9, 'box1', 1);
INSERT INTO `test2_boxes` VALUES (2, 9, 'box2', 1);
INSERT INTO `test2_boxes` VALUES (3, 4, 'box4', 3);
# --------------------------------------------------------
#
# Table structure for table `test3_boxinv`
#
CREATE TABLE `test3_boxinv` (
`id` int(11) NOT NULL auto_increment,
`item` varchar(255) default NULL,
`piece` varchar(255) default NULL,
`color` int(11) default NULL,
`quantity` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
#
# Dumping data for table `test3_boxinv`
#
INSERT INTO `test3_boxinv` VALUES (1, 'box1', 'piece13', 99, 100);
INSERT INTO `test3_boxinv` VALUES (2, 'box1', 'piece14', 99, 200);
INSERT INTO `test3_boxinv` VALUES (3, 'box1', 'piece15', 22, 300);
INSERT INTO `test3_boxinv` VALUES (4, 'box2', 'piece13', 99, 2100);
INSERT INTO `test3_boxinv` VALUES (5, 'box2', 'piece16', 44, 2200);
INSERT INTO `test3_boxinv` VALUES (6, 'box3', 'piece14', 88, 3100);
INSERT INTO `test3_boxinv` VALUES (7, 'box4', 'piece33', 99, 4100);
INSERT INTO `test3_boxinv` VALUES (8, 'box4', 'piece13', 77, 4200);
Edited 7 time(s). Last edit at 03/27/2010 11:41AM by M B.