Mysql Join 3 Tables
Mysql Join 3 Tables is used to join 3 Tables using left join. The left join
returns you only selective records which are common in tables on the basis of
common column.
Understand with Example
The Tutorial illustrate an example from 'Mysql Join 3 Tables' using Left
Join. To grasp this example we create a table 'roseindia'. The create table construct
a table 'roseindia' with fieldname and datatype.
Query to Create Table named roseindia:-
mysql> CREATE TABLE roseindia (
-> Empid int(11),
->firstname varchar(30),
-> city varchar(30)
-> );
Query OK, 0 rows affected (0.05 sec)
|
Query to insert data into Table named roseindia:-
The insertinto add the records or rows to the
table 'roseindia'.
mysql> insert into roseindia values(01,'Girish','Nainital');
Query OK, 1 row affected (0.02 sec)
mysql> insert into roseindia values(02,'Komal','Merrut');
Query OK, 1 row affected (0.00 sec)
mysql> insert into roseindia values(03,'Amit','Lucknow');
Query OK, 1 row affected (0.02 sec)
mysql> insert into roseindia values(04,'Sandeep','Lucknow');
Query OK, 1 row affected (0.03 sec)
|
Query to view data of Table named roseindia:-
mysql> select * from roseindia;
|
Output:-
+-------+-----------+----------+
| Empid | firstname | city |
+-------+-----------+----------+
| 1 | Girish | Nainital |
| 2 | Komal | Merrut |
| 3 | Amit | Lucknow |
| 4 | Sandeep | Lucknow |
+-------+-----------+----------+
4 rows in set (0.00 sec)
|
Query to Create Table newstrack:-
We create another table 'newstrack' with required field
and data type respectively.
mysql> CREATE TABLE newstrack (
-> Empid int(11),
-> firstname varchar(10),
-> email varchar(30)
-> );
Query OK, 0 rows affected (0.03 sec)
|
Query to insert data into Table named newstrack:-
The insert into add the records or rows into a
table newstrack.
mysql> insert into newstrack values(01,'Suman','girish@gmail.com');
Query OK, 1 row affected (0.02 sec)
mysql> insert into newstrack values(02,'Ravi','komal@gmail.com');
Query OK, 1 row affected (0.01 sec)
mysql> insert into newstrack values(03,'Santosh','Amit@gmail.com');
Query OK, 1 row affected (0.01 sec)
|
Query to view data of Table named newstrack:-
mysql> select * from newstrack;
|
Output:-
+-------+-----------+------------------+
| Empid | firstname | email |
+-------+-----------+------------------+
| 1 | Suman | girish.gmail.com |
| 2 | Ravi | komal.gmail.com |
| 3 | Santosh | Amit.gmail.com |
+-------+-----------+------------------+
3 rows in set (0.00 sec)
|
Query to Create Table named employee:-
We create another table 'employee' with fieldname and
datatype respectively.
mysql> CREATE TABLE employee (
->Empid int(11),
->first_name varchar(30),
->last_name varchar(15),
->start_date date,
->end_date date,
->city varchar(10),
->description varchar(15)
);
Query OK, 0 rows affected (0.05 sec)
|
Query to insert data into Table named employee:-
The insert into add the records or rows to
the table 'employee'.
mysql> insert into employee values(01,'
Girish','Tewari','2006-12-31','2010-06-25','Nainital','Programmer');
Query OK, 1 row affected (0.02 sec)
mysql> insert into employee values(02,'
Komal','Choudhry','2006-12-31','2010-04-21','Meerut','Programmer');
Query OK, 1 row affected (0.00 sec)
mysql> insert into employee values(03,'
Mahendra','Singh','2006-12-31','2007-05-12','Lucknow','Programmer');
Query OK, 1 row affected (0.02 sec)
|
Query to view data of Table named employee:-
mysql> select * from employee;
|
Output:-
+-------+------------+-----------+------------+------------+----------+-------------+
| Empid | first_name | last_name | start_date | end_date | city | description |
+-------+------------+-----------+------------+------------+----------+-------------+
| 1 | Girish | Tewari | 2006-12-31 | 2010-06-25 | Nainital | Programmer |
| 2 | Komal | Choudhry | 2006-12-31 | 2010-04-21 | Meerut | Programmer |
| 3 | Mahendra | Singh | 2006-12-31 | 2007-05-12 | Lucknow | Programmer |
+-------+------------+-----------+------------+------------+----------+-------------+
3 rows in set (0.00 sec)
|
Query to join data of above 3 Tables created
above:-
The below Query merge three tables that return you only
those selective records present in tables on the basis of common column in the
tables.
SELECT * FROM employee
LEFT JOIN roseindia ON employee.Empid = roseindia.empid
LEFT JOIN newstrack ON employee.Empid = newstrack.empid;
|
Output:-
+-------+------------+-----------+------------+------------+----------+-------------+-
| Empid | first_name | last_name | start_date | end_date | city | description |
+-------+------------+-----------+------------+------------+----------+-------------+-
| 1 | Girish | Tewari | 2006-12-31 | 2010-06-25 | Nainital | Programmer |
| 2 | Komal | Choudhry | 2006-12-31 | 2010-04-21 | Meerut | Programmer |
| 3 | Mahendra | Singh | 2006-12-31 | 2007-05-12 | Lucknow | Programmer |
+-------+------------+-----------+------------+------------+----------+-------------+-
+-------+-----------+----------+-------+-----------+------------------+
| Empid | firstname | city | Empid | firstname | email |
+-------+-----------+----------+-------+-----------+------------------+
| 1 | Girish | Nainital | 1 | Suman | girish.gmail.com |
| 2 | Komal | Merrut | 2 | Ravi | komal.gmail.com |
| 3 | Amit | Lucknow | 3 | Santosh | Amit.gmail.com |
+-------+-----------+----------+-------+-----------+------------------+
3 rows in set (0.00 sec)
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
