3 table merge with some condition

Question

I am new to this forum and I am learning mysql with the help of online resources, currently I am trying to compare and merge table with some conditions

Fiddle

These are my tables;

         mysql> show tables;
         +--------------------+
         | Tables_in_products |
         +--------------------+
         | main_info          |
         | product1           |
         | product2           |
         +--------------------+
         3 rows in set (0.00 sec)

This is my first table

         mysql> select * from main_info; 
         +------+------+-------+-------+----------+
         | key1 | key2 | info1 | info2 | date     |
         +------+------+-------+-------+----------+
         | 1    | 1    | 15    | 90    | 20120501 |
         | 1    | 2    | 14    | 92    | 20120601 |
         | 1    | 3    | 15    | 82    | 20120801 |
         | 2    | 1    | 17    | 90    | 20130302 |
         | 2    | 2    | 16    | 88    | 20130601 |
         +------+------+-------+-------+----------+
         5 rows in set (0.00 sec)

This is product table1 :

         mysql> select * from product1;
         +------+------+--------+--------------+
         | key1 | key2 | serial | product_data |
         +------+------+--------+--------------+
         | 1    | 1    | 0      | 15.556       |
         | 1    | 1    | 1      | 14.996       |
         | 1    | 1    | 2      | 12.556       |
         | 1    | 1    | 3      | 15.669       |
         | 1    | 2    | 0      | 12.556       |
         | 1    | 2    | 1      | 13.335       |
         | 1    | 3    | 1      | 12.225       |
         | 1    | 3    | 2      | 13.556       |
         | 1    | 3    | 3      | 14.556       |
         | 2    | 1    | 0      | 12.556       |
         | 2    | 1    | 1      | 13.553       |
         | 2    | 1    | 2      | 12.335       |
         +------+------+--------+--------------+
         12 rows in set (0.00 sec)

This is second product table

         mysql> select * from product2;
         +------+------+--------+--------------+
         | key1 | key2 | serial | product_data |
         +------+------+--------+--------------+
         | 1    | 1    | 0      | 5.556        |
         | 1    | 1    | 1      | 4.996        |
         | 1    | 2    | 0      | 2.556        |
         | 1    | 2    | 1      | 3.335        |
         | 1    | 2    | 2      | 2.56         |
         | 1    | 2    | 3      | 3.556        |
         | 1    | 3    | 1      | 2.225        |
         | 1    | 3    | 2      | 3.556        |
         | 2    | 2    | 0      | 2.556        |
         | 2    | 2    | 1      | 3.553        |
         +------+------+--------+--------------+
         10 rows in set (0.00 sec)

I have more than 8 product table, in which I would like to compare key1, key2 and serial, of 2 product table and main_info table, depending on the maximum I would like to merge, if product data is not exist then substitute NaN and finally would like to have 1 output like below

Expected O/P

   key1       key2       serial     info1      info2      date       product_table1_data product_table2_data
   1          1          0          15         90         20120501   15.556     5.556     
   1          1          1          15         90         20120501   14.996     4.996     
   1          1          2          15         90         20120501   12.556     NaN       
   1          1          3          15         90         20120501   15.669     NaN       
   1          2          0          14         92         20120601   12.556     2.556     
   1          2          1          14         92         20120601   13.335     3.335     
   1          2          2          14         92         20120601   NaN        2.56      
   1          2          3          14         92         20120601   NaN        3.556     
   1          3          1          15         82         20120801   12.225     2.225     
   1          3          2          15         82         20120801   13.556     3.556     
   1          3          3          15         82         20120801   14.556     NaN       
   2          1          0          17         90         20130302   12.556     NaN       
   2          1          1          17         90         20130302   13.553     NaN       
   2          1          2          17         90         20130302   12.335     NaN       
   2          2          0          16         88         20130601   NaN        2.556     
   2          2          1          16         88         20130601   NaN        3.553

Kindly someone help me to get expected result

This is structure of my database;

 $ cat product.sql
   --
   -- Table structure for table `main_info`
   --

   DROP TABLE IF EXISTS `main_info`;

   CREATE TABLE `main_info` (
     `key1` varchar(1000) DEFAULT NULL,
     `key2` varchar(1000) DEFAULT NULL,
     `info1` varchar(1000) DEFAULT NULL,
     `info2` varchar(1000) DEFAULT NULL,
     `date` varchar(1000) DEFAULT NULL
   ) ENGINE=MyISAM DEFAULT CHARSET=latin1;


   LOCK TABLES `main_info` WRITE;

   INSERT INTO `main_info` VALUES ('1','1','15','90','20120501'),('1','2','14','92','20120601'),('1','3','15','82','20120801'),('2','1','17','90','20130302'),('2','2','16','88','20130601');
   UNLOCK TABLES;


   DROP TABLE IF EXISTS `product1`;

   CREATE TABLE `product1` (
     `key1` varchar(1000) DEFAULT NULL,
     `key2` varchar(1000) DEFAULT NULL,
     `serial` varchar(1000) DEFAULT NULL,
     `product_data` varchar(1000) DEFAULT NULL
   ) ENGINE=MyISAM DEFAULT CHARSET=latin1;


   LOCK TABLES `product1` WRITE;

   INSERT INTO `product1` VALUES ('1','1','0','15.556'),('1','1','1','14.996'),('1','1','2','12.556'),('1','1','3','15.669'),('1','2','0','12.556'),('1','2','1','13.335'),('1','3','1','12.225'),('1','3','2','13.556'),('1','3','3','14.556'),('2','1','0','12.556'),('2','1','1','13.553'),('2','1','2','12.335');

   UNLOCK TABLES;



   DROP TABLE IF EXISTS `product2`;

   CREATE TABLE `product2` (
     `key1` varchar(1000) DEFAULT NULL,
     `key2` varchar(1000) DEFAULT NULL,
     `serial` varchar(1000) DEFAULT NULL,
     `product_data` varchar(1000) DEFAULT NULL
   ) ENGINE=MyISAM DEFAULT CHARSET=latin1;


   LOCK TABLES `product2` WRITE;

   INSERT INTO `product2` VALUES ('1','1','0','5.556'),('1','1','1','4.996'),('1','2','0','2.556'),('1','2','1','3.335'),('1','2','2','2.56'),('1','2','3','3.556'),('1','3','1','2.225'),('1','3','2','3.556'),('2','2','0','2.556'),('2','2','1','3.553');
   UNLOCK TABLES;

I tried this, but this is not taking care about serial maximum for each key pair and about NaN

 SELECT * 
 FROM main_info INNER JOIN product1
 ON 
 main_info.key1=product1.key1 and
 main_info.key2=product1.key2
 INNER JOIN product2 
 ON 
 product1.key1=product2.key1 and
 product1.key2=product2.key2

*Merging process detail *

1 . take key1 and key2 from main_info table 2 . search in table product1, product2, if key1 and key2 is found in any one of the table or in both the table, get the length of serial column for those keys, find maximum length,

for example in product1 table, we have 4 serial number

     +------+------+--------+--------------+
     | key1 | key2 | serial | product_data |
     +------+------+--------+--------------+
     | 1    | 1    | 0      | 15.556       |
     | 1    | 1    | 1      | 14.996       |
     | 1    | 1    | 2      | 12.556       |
     | 1    | 1    | 3      | 15.669       |

and in second table we have just 2 serial numbers, 0 and 1 only

     +------+------+--------+--------------+
     | key1 | key2 | serial | product_data |
     +------+------+--------+--------------+
     | 1    | 1    | 0      | 5.556        |
     | 1    | 1    | 1      | 4.996        |

so merge serial numbers from both the table and check whether data exists or not if exists then keep product_date else write NaN

Output should be like this when you merge 2 product tables, once this is done add info1 info2 and date column corresponding to key1 and key2

     +------+------+--------+--------------+--------------
     | key1 | key2 | serial | product_data |product_data2
     +------+------+--------+--------------+-------------
     | 1    | 1    | 0      | 15.556       |5.556
     | 1    | 1    | 1      | 14.996       |4.996
     | 1    | 1    | 2      | 12.556       |NaN
     | 1    | 1    | 3      | 15.669       |NaN

Please let me know if you need more information

Thank you.

Hi and welcome to the forum. It's really impressive the way you provide DDL and DML (if only all posters would do the same <sigh...>). You might want to consider SQLFiddle for that kind of info - then people who are good at SQL but don't have MySQL running can also help out? — Vérace, Commented Jun 11, 2014 at 8:38
Thank you, I provided since I don't want to consume more time of volunteer..... I hope you can help me.... — user3637224, Commented Jun 11, 2014 at 8:42
@user3637224 Please don't mark the question as "solved". If you found a solution, post it as an answer (yes, you can answer your own questions, it is accepted.) — ypercubeᵀᴹ, Commented Jun 12, 2014 at 9:16
@User3637224: do you to sum the produk_data from 2 difference table? — Hilarius L. Doren, Commented Sep 11, 2014 at 7:36

Giovanni · Accepted Answer · 2014-10-10 10:59:07Z

As first step I merge the two product tables. I need an full outer join on product1 and product2 tables. To obtain on outer join in mysql we can union a left join and a right join. Start with a left join. In the following query IFNULL(p1.key1, p2.key1) is useful to merge two key column coming from the two product tables and IFNULL(, 'NaN') is useful to obtain 'NaN' in the output.

select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
left join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial =     p2.serial;

To obtain a full outer join I need to repeat the previous query with 'right join' and union the result with the 'left join'

select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
left join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial
union
select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
right join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial;

For convenience, I create a view with the previous query:

create or replace view p12 as
select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
left join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial
union
select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
right join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial;

To check we can try with:

mysql> select * from p12 where key1 = 1 and key2 =1 ;
+------+------+--------+---------------+---------------+
| key1 | key2 | serial | product_data1 | product_data2 |
+------+------+--------+---------------+---------------+
| 1    | 1    | 0      | 15.556        | 5.556         |
| 1    | 1    | 1      | 14.996        | 4.996         |
| 1    | 1    | 2      | 12.556        | NaN           |
| 1    | 1    | 3      | 15.669        | NaN           |
+------+------+--------+---------------+---------------+

So the he final query is:

select mi.key1, mi.key2, p12.serial, mi.info1, mi.info2, mi.date, p12.product_data1, p12.product_data2 
from main_info mi 
inner join p12 on mi.key1 = p12.key1 and mi.key2 = p12.key2
order by mi.key1, mi.key2, p12.serial;


+------+------+--------+-------+-------+----------+---------------+---------------+
| key1 | key2 | serial | info1 | info2 | date     | product_data1 | product_data2 |
+------+------+--------+-------+-------+----------+---------------+---------------+
| 1    | 1    | 0      | 15    | 90    | 20120501 | 15.556        | 5.556         |
| 1    | 1    | 1      | 15    | 90    | 20120501 | 14.996        | 4.996         |
| 1    | 1    | 2      | 15    | 90    | 20120501 | 12.556        | NaN           |
| 1    | 1    | 3      | 15    | 90    | 20120501 | 15.669        | NaN           |
| 1    | 2    | 0      | 14    | 92    | 20120601 | 12.556        | 2.556         |
| 1    | 2    | 1      | 14    | 92    | 20120601 | 13.335        | 3.335         |
| 1    | 2    | 2      | 14    | 92    | 20120601 | NaN           | 2.56          |
| 1    | 2    | 3      | 14    | 92    | 20120601 | NaN           | 3.556         |
| 1    | 3    | 1      | 15    | 82    | 20120801 | 12.225        | 2.225         |
| 1    | 3    | 2      | 15    | 82    | 20120801 | 13.556        | 3.556         |
| 1    | 3    | 3      | 15    | 82    | 20120801 | 14.556        | NaN           |
| 2    | 1    | 0      | 17    | 90    | 20130302 | 12.556        | NaN           |
| 2    | 1    | 1      | 17    | 90    | 20130302 | 13.553        | NaN           |
| 2    | 1    | 2      | 17    | 90    | 20130302 | 12.335        | NaN           |
| 2    | 2    | 0      | 16    | 88    | 20130601 | NaN           | 2.556         |
| 2    | 2    | 1      | 16    | 88    | 20130601 | NaN           | 3.553         |
+------+------+--------+-------+-------+----------+---------------+---------------+

can you help me on this post
– user3637224
Commented Feb 21, 2016 at 18:10 — user3637224, Commented Feb 21, 2016 at 18:10

Stack Exchange Network

3 table merge with some condition

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
mysql
table
merge
or ask your own question.

Hot Network Questions

3 table merge with some condition

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysqltablemerge or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
mysql
table
merge
or ask your own question.