PHP/MySQL ->
Join between two tables in MySQL

Suppose that we want to locate employees of Colombo-Ltd according to the building they work in. Employees table does not include a number of building, but it can be retrieved from another table - departments:

USE colombo;

/*
this is a simple join between employees and departments tables
*/

SELECT e.EmpNo, e.DeptNo, e.FirstName, e.LastName, e.Salary, d.BuildingNum
FROM employees AS e INNER JOIN departments AS d
ON e.DeptNo = d.DeptNo

Query results will look like this:
EmpNoDeptNoFirstNameLastNameSalaryBuildingNum
11johnsilver2100001
21gretgarbo650001
32leechen800002
142suzanshpeer810002
43danieldefo705003

Now we can calculate total salary amount per office building using GROUP BY:
USE colombo;

/*
to show biggest amount first we need to add DESC at the end of ORDER clause
*/

SELECT t1.BuildingNum, SUM(t1.Salary) AS total_salary
FROM
(
  SELECT e.EmpNo, e.DeptNo, e.FirstName, e.LastName,
  e.Salary, d.BuildingNum
  FROM employees AS e INNER JOIN departments AS d
  ON e.DeptNo = d.DeptNo
) AS t1
GROUP BY t1.BuildingNum
ORDER BY SUM(t1.Salary) DESC;

Query results will look like this:
BuildingNumtotal_salary
4617500
1275000
3246000
2161000



sqlexamples.info