Joining more than two tables mysql examples

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:

Leave a Reply