topic Re: SQL query from 2 or more databases in Discussions

SQL query from 2 or more databases

morenopelaez_p — Fri, 12 Feb 2016 14:08:50 GMT

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

Re: SQL query from 2 or more databases

pmroz — Fri, 12 Feb 2016 14:58:42 GMT

If you're using Oracle you can create a database link between the two databases. Then you can select data from both databases simultaneously. I've used database links quite often and they are fairly painless. Might need some help from a DBA to set up.

If you're using SQL server there's something called a linked server that can accomplish the same thing, but I don't have any experience with that.

Re: SQL query from 2 or more databases

morenopelaez_p — Fri, 12 Feb 2016 16:12:22 GMT

Thanks @PMroz , that is helpful.

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

Re: SQL query from 2 or more databases

msharp — Fri, 12 Feb 2016 17:06:13 GMT

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));

Re: SQL query from 2 or more databases

morenopelaez_p — Tue, 16 Feb 2016 09:20:50 GMT

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