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.
MySQL ServerPHP

Avatar of undefined
Last Comment
Argenti

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Argenti

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
ASKER
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!
Argenti

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck