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.
sadbassaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ArgentiCommented:
Are you sure you want to do this? It will bring a LOT of data from the archives, which might visibly slow down your query execution time.

In any case, you can do it using UNION (or UNION ALL):

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

UNION

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

order by timeDate asc, jobID asc

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tomas Helgi JohannssonCommented:
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
0
sadbassaAuthor Commented:
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!
0
ArgentiCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.