Joining tables with timestamp

[torhalvar]

Hi,

first of all I'm sorry for my English.

I am building a database were I want to join three tables.

Employee:

ID varchar(7)
LastName
Forename
Address
Zip
Email
....

employee_salary:

ID int(50)
employeeID varchar(7) (key from employee)
salary_from (timestamp)
salary (double)

Workinghours:

ID int(50)
employeeID varchar(7)
workStart timestamp
workStop timestamp


What I want is to join the three tables in a way that each line from working hours is joined with the correct salary and the correct person. The salary_from in table salary is the key to the sollution.

The point is that the salary can change, and when it does, a date (timestamp) is saved next to the new salary. So when you combine the working hours and the employee_salary data, you should always get the right salary for the hours from workingHours table.

Ask if this is not clear.

Thank you.

 

[JarlH]

Is it possible that one row in the workinghours table includes hours with two different salaries?

 

[torhalvar]

It is possible, but a new salary is supposed to be effective from 00:00 and usually there is no work over midnight. So there is basically only one salary per start-end.

 

[JarlH]

Try something like:
[tt]
SELECT wh.*,
e.lastname,e.firstname,
(SELECT es.salary FROM employee_salary es
WHERE es.employeeID = ws.employeeID
AND es.salary_from = (SELECT MAX(salary_from)
FROM employee_salary
WHERE salary_from <=
wh.workStart))
FROM workinghours AS wh,
employee AS e
WHERE ws.employeeID = e.id;
[/tt]
(Not tested at all...)