Link to home
Start Free TrialLog in
Avatar of sadbassa
sadbassa

asked on

Multi Table Data

Hi,

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

Link to home
membership
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
Hi!

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 activedbname.jobs J on J.jobID = T.jobID
where T.userID ='$userID' and (timeDate >= '$startDate' and timeDate <= '$endDate')

UNION

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

Regards,
     Tomas Helgi
Avatar of sadbassa
sadbassa

ASKER

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.