Using query result field as database name in same

问题:

Probably a long shot with this...

I have two databases "Job Register" and "Job001".

I want to retrieve a list of the jobs that have had a meeting, and the last time the meeting took place (which is determined by the latest revision of a certain document).

The list of jobs that have had a meeting is easy enough as they are in the same database and have a specific column for this information. The tricky bit is then to have an additional column retrieved that uses the retrieved job number to JOIN that job's database and check a document number "DOC001" in this case for it's latest revision and retrieve the "date_modified".

Please see the attached image of the two databases and the desired query result.

回答1:

Your question is unclear a bit, but I think you are looking for

SELECT JR.JobNumber,
       JR.IsComplete,
       J.DateModified
FROM JobRegister JR INNER JOIN Job001 J
ON JR.JobNumber = J.JobNumber
WHERE DocumentRevision = 'B'; 

Also JobNumber in both tables should be the same datatype.

Demo

回答2:

SELECT JR.JobNumber,
       JR.IsComplete,
       J.DateModified
FROM JobRegister JR INNER JOIN CONCAT('Job', JR.JobNumber) J
ON JR.JobNumber = J.JobNumber
WHERE DocumentRevision = 'B';