Here you will learn about joining three tables in sql with example.
Let us consider three tables Employee, Department and Project. We will see the complete example wherein these 3 tables are joined to produce a result:
Table Employee
This table records the Id of the Employee which is the primary key, the name of the working Employee, the Department Id of the Department they are working in and the Project they are working for.
- EId – Primary key which holds Id of the Employee.
- EName – Name of the Employee.
- DId – Department Id of the Department, the Employee is working in.
- PId – Project Id of the project the Employee is working for.
Table creation:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE Employee(EId int primary key, EName varchar(20), DId int, PId int) INSERT INTO Employee VALUES(1, Ramesh) INSERT INTO Employee VALUES(2, Somesh) INSERT INTO Employee VALUES(3, Rajesh) INSERT INTO Employee VALUES(4, Ram) INSERT INTO Employee VALUES(5, Ishi) INSERT INTO Employee VALUES(6, Rekha) INSERT INTO Employee VALUES(7,Mukesh) |
This table looks like:
| EId | EName | DId | PId |
| 1 | Ramesh | 3 | 1 |
| 2 | Somesh | 2 | 3 |
| 3 | Rajesh | 1 | 5 |
| 4 | Ram | 3 | 4 |
| 5 | Ishi | 2 | 3 |
| 6 | Rekha | 4 | 2 |
| 7 | Mukesh | 1 | 5 |
Table Department
This table contains the Department an Employee Its attributes are:
- DId – This is the Department Id of the Department and it is the primary key in this table.
- DName- This holds the name of the Department.
Table creation:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE Department(DId int primary key, DName varchar(20)) INSERT INTO Department VALUES(1, Manufacturing) INSERT INTO Department VALUES(2, HR) INSERT INTO Department VALUES(3, RnD) INSERT INTO Department VALUES(4, Accounting) INSERT INTO Department VALUES(5, IT) |
| DId | DName |
| 1 | Manufacturing |
| 2 | HR |
| 3 | RnD |
| 4 | Accounting |
| 5 | IT |
Table Project
Holds the Project name an Employee is dealing in. It contains the following attributes:
- PId – Project Id, the primary key of the table.
- PName – Project N
Table Creation:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE Project(PId int primary key, PName varchar(20) ) INSERT INTO Project VALUES(1, Machine Learning) INSERT INTO Project VALUES(2, Taxes) INSERT INTO Project VALUES(3, AskHR Portal) INSERT INTO Project VALUES(4, Blockchain) INSERT INTO Project VALUES(5, CAD) INSERT INTO Project VALUES(6, PR) |
| PId | PName |
| 1 | Machine Learning |
| 2 | Taxes |
| 3 | AskHR Portal |
| 4 | Blockchain |
| 5 | CAD |
| 6 | PR |
Now, we have already created the three tables we need to work on. Let us consider a case where we need to display the Employee Name, the Department Name and the Project Name together, we would require to join the three tables:
- Apply INNER JOIN on the first two tables.
- Apply INNER JOIN on the resultant of the two tables and the third table.
If, we have more than three tables with us, we can simply extend the same procedure to multiple tables, i.e, take the resultant of (n-1) tables and join with the nth table.
As far as this example is concerned, we will have the QUERY as:
|
1 2 3 4 5 6 7 8 9 |
SELECT Employee.EId, Employee.EName, Department.DName, Project.PName FROM ( (Employee INNER JOIN Department ON Employee.DId = Department.DId) INNER JOIN Project ON Employee.PId = Project.PId); |
The resultant table would be:
| EId | EName | DName | PName |
| 1 | Ramesh | RnD | Machine Learning |
| 2 | Somesh | HR | AskHR Portal |
| 3 | Rajesh | Manufacturing | CAD |
| 4 | Ram | Rnd | Blockchain |
| 5 | Ishi | HR | AskHR Portal |
| 6 | Rekha | Accounting | Taxes |
| 7 | Mukesh | Manufacturing | CAD |
Hence, we can conclude that the joining of the table is as easy as it is important. Once we get familiar with joining two tables, we won’t have to worry about joining multiple tables.
