MySQL Forums :: Newbie :: join in union


Advanced Search

join in union
Posted by: M B ()
Date: March 27, 2010 04:49AM

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.

Options: ReplyQuote


Subject Written By Posted
join in union M B 03/27/2010 04:49AM
Re: join in union Peter Brawley 03/27/2010 10:35AM
Re: join in union M B 03/27/2010 11:45AM
Re: join in union Rick James 03/28/2010 12:33PM
Re: join in union M B 03/28/2010 02:53PM
union and missing limits M B 04/08/2010 01:02AM
Re: union and missing limits M B 04/08/2010 01:41AM
Re: union and missing limits Rick James 04/08/2010 07:51AM
Re: union and missing limits M B 04/08/2010 09:11AM
Re: union and missing limits Rick James 04/09/2010 12:33AM
Re: union and missing limits M B 04/09/2010 03:59AM
Re: union and missing limits Rick James 04/09/2010 09:42AM
Re: union and missing limits M B 04/09/2010 01:06PM
Re: union and missing limits Rick James 04/09/2010 09:41PM
Re: union and missing limits M B 04/12/2010 03:48AM
Re: join in union Guelphdad Lake 04/08/2010 07:56AM
Re: join in union Rick James 04/08/2010 08:46AM


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.