[thelist] mySQL Query Trouble
Beau Hartshorne
beau at hartshorne.ca
Sat Nov 15 00:41:32 CST 2003
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
More information about the thelist
mailing list