I have a job log system which has been developed over a number of years and part of it features the option of producing a user timesheet for billing.
To access the report data we use the following:
select timesheet.*, jobs.clientID from timesheet
inner join jobs on jobs.jobID = timesheet.jobID where timesheet.userID ='$userID' and (timeDate >= '$startDate' and timeDate <= '$endDate') order by timeDate asc, jobID asc
Which produces the required results. The difficulty I have was that when designed, the job and time entries can be archived, which involves them physically being moved to another database table. The field names are identical, in fact the tables are identical.
So my question is: Is there any way that I can amend the query above to pull the same information in from both the "timesheet" and "jobs" as above, while also querying the "archive_timesheet" and "archive_jobs" table at the same time to include the data from the relevant date range from these as well.
Thanks in advance.