SQL query from 2 or more databases

Question

I have been creating some queries to an SQL database to extract some of the information from a master table and some other linked tables in a database.

Now I want to be able to create a query that links tables located in two separate databases (both located in the same server) but I do not know if this is possible.

When you create a query, the connection seems to be linked to a specific database in a server and NOT to the server itself. This makes the tables available for the query limited to only one of the databases.

Is this correct or am I missing something?

Is there a way around it? I have not tried to create two connections (one per database), would this work/be possible?

Thanks in advance

morenopelaez_p · Answer

Thanks msharp, that was my backup option indeed.

After doing some research it looks like there is no equivalent to Oracle's "dblink" in MySQL. I still have to try to create a view table from several and call that one database from JMP. That could work as a "summary".

msharp · Answer

Echoing PMroz's answer, this is really more of a DBA question.  At least as far as querying two databases at once goes.  Other wise, you could always query both databases separately and then join/update/concatenate the tables together.

dbhandle1 = Create Database Connection(info1);

sql1 = "";

table1 = Execute SQL(dbhandle1, sql1, "Table1");

dbhandle2 = Create Database Connection(info2);

sql2 = "";

table2 = Execute SQL(dbhandle2, sql2, "Table2");

table1 << Join(with(table2));

morenopelaez_p · Answer

Thanks @PMroz , that is helpful.

I am using MySQL so I will investigate what is the equivalent to the "linking" procedures you mention.