union, group, join...?

[mabog]

Need help with my query. I think the answer is union and/or join but how?

 

Example tables:

mysql> select * from test1_loose;
+----+---------+------+----------+-------+
| id | piece   | user | quantity | color |
+----+---------+------+----------+-------+
|  1 | piece13 |    9 |     9100 |    99 |
|  2 | piece19 |    9 |     9200 |    66 |
+----+---------+------+----------+-------+


mysql> select * from test2_boxes;
+----+----------+----------+--------------+
| id | own_user | own_item | own_quantity |
+----+----------+----------+--------------+
|  1 |        9 | box1     |            1 |
|  2 |        9 | box2     |            1 |
|  3 |        4 | box4     |            3 |
+----+----------+----------+--------------+

mysql> select * from test3_boxinv;
+----+------+---------+-------+----------+
| id | item | piece   | color | quantity |
+----+------+---------+-------+----------+
|  1 | box1 | piece13 |    99 |      100 |
|  2 | box1 | piece14 |    99 |      200 |
|  3 | box1 | piece15 |    22 |      300 |
|  4 | box2 | piece13 |    99 |     2100 |
|  5 | box2 | piece16 |    44 |     2200 |
|  6 | box3 | piece14 |    88 |     3100 |
|  7 | box4 | piece33 |    99 |     4100 |
|  8 | box4 | piece13 |    77 |     4200 |
+----+------+---------+-------+----------+

 

Result should look like this:

 

user  piece     color   loose_quantity   box_quantity
9     piece13   99	   9100             2200 (100 from box1) +  (2100 from box2)
9     piece19   66	   9200
9     piece14   99                       200
9     piece15   22                       300
9     piece16   44                       2200
4     piece33   99                       12300 (user 4 has three box4, so it's 3x 4100)
4     piece13   77                       12600 (user 4 has three box4, so it's 3x 4200)

 

Explanation:

Users can have loose pieces or pieces in boxes. If piece, color and user is equal (loose or boxed), data should be combined.

test1_loose table is for loose pieces,

test2_boxes table defines what boxes users have (and quantity of boxes) and

test3_boxinv is the table for pieces in boxes.

 

This query is what I have now, but it doesn't show user 4's data and user 9's data is doubled.

 

SELECT

test1_loose.user,

test1_loose.piece,

test1_loose.color,

test3_boxinv.piece,

test3_boxinv.color,

test1_loose.quantity AS loose_quantity,

test3_boxinv.quantity AS box_quantity

FROM

test1_loose,test3_boxinv,test2_boxes

LEFT JOIN (test3_boxinv t3) ON (t3.piece = test1_loose.piece AND test1_loose.color = t3.color)

WHERE

test2_boxes.own_user = test1_loose.user AND

test2_boxes.own_item = test3_boxinv.item

 

[mabog]

OK, first example is not possible - one total quantity is enough.

 

user  piece     color   total_quantity
9     piece13   99	   11300 (9100 loose + 100 from box1 + 2100 from box2)
9     piece19   66	   9200
9     piece14   99        200
9     piece15   22        300
9     piece16   44        2200
4     piece33   99        12300 (user 4 has three box4, so it's 3x 4100)
4     piece13   77        12600 (user 4 has three box4, so it's 3x 4200)

[mabog]

I'm getting there:

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

 

+------+-------+---------+----------+
| 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 still needs some joining?

 

[mabog]

Please help me with this joining, I don't get it.

 

I have tried something like this:

 

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

LEFT JOIN test3_boxinv ON test2_boxes.own_item = test3_boxinv.item

WHERE test2_boxes.own_item = test3_boxinv.item

) as ss

 

But nothing happens.

 

It's very important that query joins all items which have same user, color and item in second select.

 

[mabog]

OK, I read the quidelines now.. 

 

MySQL Server version: 4.1.25

 

Create tables & inserts:

 

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

   

[fenway]

You can't refer to LEFT JOIN-ed column values in a WHERE clause, since they might be null -- you need these in the ON condition.

[mabog]

I got answered to this from elsewhere.

SELECT  user, color, piece, SUM(quantity) AS quantity
    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 
    GROUP BY user, color, piece;

 

Thanks anyway.