Here is a simplified example of what I'd like to do:
SELECT a.create_time, b.create_time FROM DOB.dob_state AS a JOIN numID.numID_history AS b ON a.create_time = b.create_time;
- I'd like to join THREE tables instead of just two (each in its respective database on the same server), and I'm not sure how to do that concisely. The third database.table is entity.entity AS c. Where do I add that into the join?
Moving to slightly more complicated...I actually want to match on three fields, each of which will be included in the tables of the three databases: hash (int), create_time (timestamp), and update_time (datetime). How would I do this in a cost-efficient way?
In designing the three databases, is it essential that I use the same hash ID in all three databases or is there a way to link the three hashes (each referring to the same record) to a master ID in another database? That is for a related issue that I'll mention elsewhere, but it might affect the query process, so I'm including that consideration as well.
I am joining tables that have actual relationships with each other, but separate databases is part of the security strategy, which also includes encryption, etc. I'm looking to eventually figure out a way to do this same thing with databases on different servers, but this is just a start.