[thelist] mySQL Query Trouble

[Beau Hartshorne]

Hi all,

I have a table structure set up something like this:

employee
========
employee_id
name

pay_history
===========
pay_history_id
employee_id
amount
from_date
thru_date

timesheet
=========
timesheet_id
employee_id
work_date
hours

The pay_history defines an employee's salary. The thru_date is the only
field that can be null (in the case where the pay_history record is
still active). A new pay_history record is assigned whenever an employee
receives a raise, so one employee may have several pay_history records
associated with them. An employee also has many timesheet records
associated with them.

I am trying to write a query that finds any individual timesheet records
that have a work_date that falls outside of any pay_history record date
range. I have been able to write a query that finds timesheet records
that fall *within* all of the pay_history record date ranges, but not
the other way around. I am using mySQL 4.0.15, so have access only to
JOIN, UNION and TEMPORARY TABLEs.

Here is the query that will find timesheet records *within* the
pay_history record date ranges:

SELECT timesheet.work_date AS work_date,
timesheet.timesheet_id AS timesheet_id,
employee.employee_id AS employee_id,
employee.name AS name,
pay_history.from_date AS from_date,
pay_history.thru_date AS thru_date,
FROM pay_history
LEFT JOIN employee ON (pay_history.employee_id = employee.employee_id)
LEFT JOIN timesheet ON (employee.employee_id = timesheet.employee_id)
WHERE (pay_history.employee_id = 14) 
AND (timesheet.work_date >= '2003-01-01') 
AND (timesheet.work_date <= '2003-10-31') 
AND (timesheet.work_date >= pay_history.from_date)
AND (timesheet.work_date <= pay_history.thru_date 
	OR pay_history.thru_date IS NULL)

I think if I had access to sub selects, something that would work would
be:

SELECT * FROM timesheet WHERE NOT IN(
SELECT timesheet.work_date AS work_date,
timesheet.timesheet_id AS timesheet_id,
employee.employee_id AS employee_id,
employee.name AS name,
pay_history.from_date AS from_date,
pay_history.thru_date AS thru_date,
FROM pay_history
LEFT JOIN employee ON (pay_history.employee_id = employee.employee_id)
LEFT JOIN timesheet ON (employee.employee_id = timesheet.employee_id)
WHERE (pay_history.employee_id = 14) 
AND (timesheet.work_date >= '2003-01-01') 
AND (timesheet.work_date <= '2003-10-31') 
AND (timesheet.work_date >= pay_history.from_date)
AND (timesheet.work_date <= pay_history.thru_date 
	OR pay_history.thru_date IS NULL))

Any insight anyone might have into this would be great!!

Thanks,

Beau