Joining More than two tables – MySql Examples:
Create the tables and insert the sample values by running the below queries,
- empIdName table contains empId and empNamecode
- empIdSalary table contains empId and empSalaryCode
- empCodeSalary table contains empSalaryCode and empSalary
- empNameSalary table contains empNameCode and empName
[plain gutter=”false”]
create table empIdName (empId int, empNameCode int)
create table empCodeName (empNameCode int, empName varchar2(20))
create table empIdSalary (empId int, empSalaryCode int)
create table empCodeSalary (empSalaryCode int, empSalary int)
insert into empIdName (empId,empNameCode) values (1,5)
insert into empIdName (empId,empNameCode) values (2,3)
insert into empIdName (empId,empNameCode) values (3,10)
select * from empIdName
insert into empIdSalary (empId,empSalaryCode) values (1,2)
insert into empIdSalary (empId,empSalaryCode) values (2,7)
insert into empIdSalary (empId,empSalaryCode) values (3,9)
select * from empIdSalary
insert into empCodeSalary (empSalaryCode,empSalary) values (2,’2000′)
insert into empCodeSalary (empSalaryCode,empSalary) values (7,’4000′)
insert into empCodeSalary (empSalaryCode,empSalary) values (9,’3000′)
select * from empCodeSalary
insert into empCodeName (empNameCode,empName) values (5,’Naveen’)
insert into empCodeName (empNameCode,empName) values (3,’Chudar’)
insert into empCodeName (empNameCode,empName) values (10,’Aravind’)
select * from empCodeName
[/plain]
Expected Result:
Employee Id Employee Name Employee Salary 1 Naveen 2000 2 Chudar 4000 3 Aravind 3000
Query:
SELECT empidName.empId as "Employee Id", ecn.empName as "Employee Name",ecs.empSalary as "Employee Salary" FROM empidName INNER JOIN empIdSalary P1 ON P1.empId = empIdName.empId INNER JOIN empCodeSalary ecs ON ecs.empSalaryCode = p1.empsalarycode INNER JOIN empcodename ecn ON ecn.empnamecode = empIdName.empnamecode
Query Explanation:
- Employee id taken from the empidname table
- Employee name is taken by joining empnamecode from empidname table with empnamecode from empcodename table.
- Employee salary is taken by joining empsalarycode from empidsalary table with empsalarycode from empcodesalary table.
Recommended MySQL Books:
