The task is to retrive list of all sales persons
with total amount of their sales. We'll want to include in the list
employees that did not produced sales. In this query we will need to use
LEFT JOIN between Employees and Invoice
table. LEFT JOIN belongs to Outer Join's group and is not part of ANSI SQL,
but it works in MS-Access, SQLServer and MySQL.
SELECT a.EmpNo, TRIM(a.FirstName) & ' ' & TRIM(a.LastName) AS EmpName,
SUM(b.DocTotal) AS STotal
FROM Employees AS a LEFT JOIN Invoice AS b
ON a.EmpNo = b.SlpCode
WHERE a.DeptNo = 5
GROUP BY a.EmpNo, TRIM(a.FirstName) & ' ' & TRIM(a.LastName);
The query returns results like these:
sam helsing was newlly employed and have no sales produced in 1999, but
LEFT JOIN keeps him in the list.