Home Sql Sqljoin Mysql Join 3 Tables
Questions:Ask|Latest


 
 

Share on Google+Share on Google+

Mysql Join 3 Tables

Advertisement
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.

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)
Advertisement

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: January 16, 2009

Ask Questions?    Discuss: Mysql Join 3 Tables   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
anand
May 25, 2011
comment

good way 2 teach
vayuv
September 16, 2011
Join on 3 tables

According to the query given here ,all the three tables are not joined at a time and retrieved at a time.Its just joining two tables at a time
Sohan
September 16, 2011
table join

That is good example for learn how to join two and more tables in mysql with using php
sarath
December 2, 2011
joint query

how to joint three table in my sql
MR. Chien
February 10, 2012
thanks

thanks you !
pradeep
March 14, 2012
rgarding join of 4 tables

i want to learn join 4 tables . please help me.
billy
June 3, 2012
database

mmmmm.....i like this.... i'm in love with databases
Tibin V Paul
July 28, 2012
Joining 3 Coloumns

The post is very useful. Thank you :-)
DMCA.com