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 Link to comment https://forums.phpfreaks.com/topic/215245-simple-db-interaction/ 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" Link to comment https://forums.phpfreaks.com/topic/215245-simple-db-interaction/#findComment-1119477 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.