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.

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)

Source: http://www.roseindia.net/sql/sqljoin/mysql-join-3-tables.shtml

Advertisements

About amitsonikhandwa
I am a web developer working in PHP and MYSQL with AJAX, jQuery and JavaScript. I am in web development since 2007.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s