How to "inner join" two website DB's

Question

I have a requirement and would like to know the best way of achieving the goal.

The idea is to "inner join" two tables from two databases from two completely different websites.

For example, I have a website that tables ID's from another source. I then want to analyse those ID's in more detail by joining my table with the other websites table.

My initial thought would be to pass a JSON list of ID's from my table to a php file on the 3rd party host, which would then do a "select fields from table where id in (JSONList)", then pass back the information of which I would then stitch together and display.

Is there a better / easier way of doing this?

Answer 1

You can do it directly in the database, which allows you to use sql.

MySql will allow you to create a Federated Table locally, that when accessed will pull the data automatically from the remote database.

Once you've got your federated table setup you should be able to write your join normally e.g:

select * from table1 inner join table2 on table1.joinkey = table2.joinkey;

There are a number of related questions already if this is the approach you wish to take (or at least try):

MySQL: SELECT from another server

I need to join table from other database and sometimes other server

https://dba.stackexchange.com/questions/81411/select-in-federated-table-is-to-slow

---

Unfortunately the docs say this will only currently work for mysql to mysql: http://dev.mysql.com/doc/refman/5.1/en/federated-create-connection.html