SQL Simple -> Using Outer Join

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:
EmpNoEmpNameSTotal
8dona fiber15689.5
9leonardo rota3667.5
10aiva pesh23114.1
13sam helsingNULL

sam helsing was newlly employed and have no sales produced in 1999, but LEFT JOIN keeps him in the list.


sqlexamples.info