Simple database interaction - if you know how.

[johnsmith153]

I have 2 tables and am trying to conduct an SQL query. I simply need to know how to use a JOIN when two fields in each table are needed to match the records up. I would rather not create another field in each table.

 

I have put my query below which would work other than for what is obviously not possible ("ON xyz=xyz && abc=abc" etc.)

 

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

 

 

Each department has employees starting from number 1 so two fields are needed in each table to match the records up.

 

SELECT t1.name, (t1.wage - t2.deduction) FROM $tableName1 AS t1 LEFT JOIN $tableName2 AS t2 ON t1.employeeDept=t2.employeeDept && t1.deptEmployeeRef=t2.deptEmployeeRef WHERE t2.monthNo = '32'

 

So this should return:

Dave Smith 2000

Robert Brown 2200

 

[Octo]

I think you should considering laying out like this:

 

Table 1 "Employees"

 

employeeID/employeeDept / deptEmployeeRef / name / wage

 

1 / A / 2 / Dave Smith / 2000

2 / B / 2 / Robert Brown / 2500

 

 

Table 2 "Deductions"

 

monthNo / employeeID / deduction

 

32 / 0 / 300

32 / 1 / 300

32 / 2 / 300

33 / 3 / 500

 

 

employeeID should be auto-incrementing so it's unique - no two employees will have the same. Having a ref number per dept. might be what you need, I don't know the situation, but having a unique identifier for each entry is advisable, the rest is changeable data. This would make your JOIN statement easier

[johnsmith153]

Thanks, but as I mentioned in the first line of my question I don't really want to change the database structure just because I can't do a query. I'll do this if the query isn't possible, but I bet it is.

[Octo]

Ack, sorry, I thought it read you didn't want to add another table for some reason. My apologies.