johnsmith153 Posted October 5, 2010 Share Posted October 5, 2010 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 Quote Link to comment Share on other sites More sharing options...
joel24 Posted October 6, 2010 Share Posted October 6, 2010 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" Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.