I have two tables, one 'employees' and the other 'hide_dates'.
The 'employee' table has several fields (I need to seclect all of them *)
The 'hide_dates' table's fields are - un_id, hide_from, hide_to and employee_id.
What happens is when a employee is deleted from the site, a 'hide_from' (date) is inserted into 'hide_dates' with the employee_id also. The employee table is unchanged. This is used to hide the employee untill they are undeleted. When they are undeleted the field 'hide_to' (date) is inserted. And they should now become avialable agian on the site.
This allows to track history on employees, so we can have them hidden several times.
The idea is that when a date is selected, we show all employees that were available (unhidden) for the date (the date specifies the first day of the week). I still need to show the hidden employees for the week they were deleted and/or undeleted (as deltails are stored for each day, and we must show all details when they were active).
What I need is the SQL to select this, I think I need a sub query something like this:
main query:
SELECT E.ID as emp_id,
E.FirstName as emp_first,
E.LastName as emp_last,
E.EmpNumber as emp_number,
E.BaseHours as emp_base
FROM employees E
WHERE E.ID
NOT IN
(// sub query results)
ORDER BY E.orderby
sub query:
SELECT employee_id
FROM hide_dates
WHERE ( '$date' BETWEEN hide_from AND hide_to ) // this part need sorted
The part I cant get is what to put in the WHERE clause above, I need to select all employee_id where it is hidden.
any help much appreciated.
