Simple db interaction

[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 / 300

32 / C / 2 / 300

32 / A / 3 / 300

33 / B / 2 / 500

[joel24]

you'll want to incorporate a subquery selecting the deductions corresponding to that employee.

$monthNumber = 32;
$sql = "SELECT e.employeeDept, e.deptEmployeeRef, e.name, e.wage - (SELECT COUNT(deduction) FROM deductions WHERE deptEmployeeRef = e.deptEmployeeRef AND monthNo = $monthNumber) FROM employees e"