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.

Answer 1

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         |
+------+------+--------+-------+-------+----------+---------------+---------------+