ICE786 Blog We train the people
Assalam-o-Alaikum!
Dear friends hope you will be fine. Today we will discuss on a problem which will make our work easy. Our some coding of PHP will reduce. Today we are going to learn a MySQL query technique in which you will fetch records from multiple tables and show in one column. So let’s start.
Problem:
Suppose you have three (3) tables ‘2017-12-26’, ‘2017-12-27’ and ‘2017-12-28’ and you need all the data in these tables with WHERE match and then show it into one column, I mean in one list.
Solution:
You need to use UNION technique of MySQL.
How:
Here is table structure
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
CREATE TABLE `2016-12-26` ( `id` int(11) NOT NULL AUTO_INCREMENT, `my_value` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; INSERT INTO `2016-12-26` VALUES ('1', 'Value 1 from 12-26'); INSERT INTO `2016-12-26` VALUES ('2', 'Value 2 from 12-26'); CREATE TABLE `2016-12-27` ( `id` int(11) NOT NULL AUTO_INCREMENT, `my_value` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; INSERT INTO `2016-12-27` VALUES ('1', 'Value 1 from 12-27'); INSERT INTO `2016-12-27` VALUES ('2', 'Value 2 from 12-27'); CREATE TABLE `2016-12-28` ( `id` int(11) NOT NULL AUTO_INCREMENT, `my_value` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; INSERT INTO `2016-12-28` VALUES ('1', 'Value 1 from 12-28'); INSERT INTO `2016-12-28` VALUES ('2', 'Value 2 from 12-28'); |
As you can see we have three tables and some data in it. Now we want all records that’s id=1 from all of these tables and then show it into one column or in list. Here we will use UNION method between three tables like this
|
1 2 3 |
SELECT id,my_value from `2016-12-26` WHERE id=1 UNION SELECT id,my_value from `2016-12-27` WHERE id=1 UNION SELECT id,my_value from `2016-12-28` WHERE id=1 ; |
Now run the command so you will see this three records. Their ID will be 1 but values are different because these are not from one table. Hope you understand.
See you in next tutorial.
