Link to home
Start Free TrialLog in
Avatar of sadbassa

asked on

Multi Table Data


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 

Open in new window

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.
Avatar of Argenti
Flag of France image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Tomas Helgi Johannsson

If your archive tables resides in another mysql database the only thing missing from the query from Argenti is the database prefix :)

select T.*, J.clientID from activedbname.timesheet T
          inner join J on J.jobID = T.jobID
where T.userID ='$userID' and (timeDate >= '$startDate' and timeDate <= '$endDate')


select T.*, J.clientID from archivedbname.archive_timesheet T
          inner join archivedbname.archive_jobs J on J.jobID = T.jobID
where T.userID ='$userID' and (timeDate >= '$startDate' and timeDate <= '$endDate')

order by timeDate asc, jobID asc

     Tomas Helgi
Avatar of sadbassa


Thanks Argenti, that solution worked.

I had to amend a function that printed the the Job Title to the screen based on ID, to query both tables, but past that all is great!
I'm glad it worked. Thank you for the points.
However, Tomas has a good point too. If archive tables were placed in an external DB, consider prefixing your table-names accordingly.