Menu
Sqlfiddle
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 : Answer for comment
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.
735
try this
select T.key1,T.key2,T.serial,T.info1,T.info2,T.date,p1.product_data,p2.product_data
from
(SELECT m.key1 as key1,m.key2 as key2,m.info1 as info1,m.info2 as info2 ,m.date as date,p1.serial as serial
from main_info as m
join product1 as p1 on m.key1=p1.key1 and m.key2=p1.key2
union
SELECT m.key1 as key1,m.key2 as key2,m.info1 as info1,m.info2 as info2 ,m.date as date,p2.serial as serial
from main_info as m
join product2 as p2 on m.key1=p2.key1 and m.key2=p2.key2
) as T
left join product1 as p1 on T.key1=p1.key1 and T.key2=p1.key2 and p1.serial=T.serial
left join product2 as p2 on T.key1=p2.key1 and T.key2=p2.key2 and p2.serial=T.serial
Edit or
select T.key1,T.key2,T.serial,m.info1,m.info2,m.date,p1.product_data,p2.product_data
from
(SELECT p1.key1 as key1 ,p1.key2 as key2 ,p1.serial as serial
from product1 as p1
union
SELECT p2.key1 as key1,p2.key2 as key2,p2.serial as serial
from product2 as p2
) as T
left join product1 as p1 on T.key1=p1.key1 and T.key2=p1.key2 and p1.serial=T.serial
left join product2 as p2 on T.key1=p2.key1 and T.key2=p2.key2 and p2.serial=T.serial
left join main_info as m on T.key1=m.key1 and T.key2=m.key2
Try this
SELECT * FROM ( SELECT main_info.* ,
product1.product_data as product_table1_data,
IF(product2.product_data IS NULL,"NaaN",
product2.product_data) as product_table2_data, product1.serial
FROM product1
LEFT JOIN product2 ON product1.key1 = product2.key1
AND product1.key2 = product2.key2
AND product1.serial=product2.serial
INNER JOIN main_info on
main_info.key1=product1.key1 and
main_info.key2=product1.key2
UNION
SELECT main_info.* ,
IF(product1.product_data IS NULL,"NaaN",product1.product_data) as product_table1_data,
product2.product_data as product_table2_data,
product2.serial
FROM product1 RIGHT JOIN product2 ON product1.key1 = product2.key1
AND product1.key2 = product2.key2
AND product1.serial=product2.serial
INNER JOIN main_info on
main_info.key1=product2.key1 and
main_info.key2=product2.key2) AS TEMP ORDER BY TEMP.key1,TEMP.key2,TEMP.serial
answered Dec 18 '25 03:12
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With