LEFT JOIN multiple records in right table

[johnsmith153]

I have tried for ages but can't get a LEFT JOIN to work on this. I want ALL table 1 records listed and done so any records in table 2 (that apply) are taken into account (and the deduction amount removed from wages).

 

If month is 32 now, then the query would return:

Dave Smith / 2000

Robert Brown / 2200

 

 

Table 1 "Employees"

 

employeeDept / deptEmployeeRef / name / wage

 

A / 2 / Dave Smith / 2000

B / 2 / Robert Brown / 2500

 

 

Table 2 "Deductions"

 

monthNo / employeeDept / deptEmployeeRef / deduction

 

32 / B / 2 / 200

32 / B / 2 / 100

32 / C / 2 / 300

32 / A / 3 / 300

33 / B / 2 / 500

[mikosiko]

try :

select a.employeeDept,
       a.deptEmployeeRef,
       a.name,
       a.wage -  SUM(IFNULL(b.deduction,0)) AS wages
FROM employees AS a 
   LEFT JOIN deductions AS b 
        ON a.employeeDept = b.employeeDept
        AND a.deptEmployeeRef = b.deptEmployeeRef
        AND b.monthNo = 32
GROUP BY a.employeeDept, a.deptEmployeeRef;