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','[email protected]');
Query OK, 1 row affected (0.02 sec)

mysql>  insert into newstrack values(02,'Ravi','[email protected]');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into newstrack values(03,'Santosh','[email protected]');
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)

Mysql Join 3 Tables

Mysql Join 3 Tables

Mysql Join 3 Tables

Tutorials