How can I create a join on three tables, each from a different database?

Question

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;

1. 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?

2. 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?

3. 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.

Answer 1

If you are using MySQL you can do this:

select * 
from dob.dob_state 
join numid.numid_history as t2 
    on t2.create_time = dob_state.create_time 
    and t1.last_name = t2.last_name 
    and t1.first_name = t2.first_name
join entity.entity as t3 
    on t3.create_time = t2.create_time
    and t2.last_name = t3.last_name 
    and t2_first_name = t3_last_name;

Tip: You don't have to write inner join, just join.

Answer 2

Performance implications aside, to join 3 tables from 3 different databases you would join by referencing the database as part of the table name such that:

SELECT a.col1, b.col2
FROM db1.table1 as a
INNER JOIN db2.table2 as b
   on a.col1 = b.col2
INNER JOIN db3.table3 as c
   on c.col1 = b.col1

Also, you sound like you're joining tables that have actual relationships with each other that require constraints (see HashID) in which case, I would strongly suggest you try to use the same database but multi-part name your tables instead.