Re: [Squirrel-sql-users] joining tables across multiple databases

[Andreas O. <cpt...@gm...>]

Hi Stan,

if the databases are part of the same database servers you can use 
qualified table names. The example below should explain how to use 
qualified table names

--Create two new test databases
create database db1;
create database db2;
--Create tables in both databases
use db1;
create table master
(
   id int,
   info varchar(255)
);
use db2;
create table detail values
(
   id int,
   masterid int,
   info varchar(255)
);
--create sample data
use db1;
insert into master values(1, 'master values A');
insert into master values(2, 'master values B');
insert into master values(3, 'master values C');
use db2;
insert into detail values(1,1, 'detail values A');
insert into detail values(2,1, 'detail values B');
insert into detail values(3,1, 'detail values C');
insert into detail values(4,2, 'detail values D');
insert into detail values(5,2, 'detail values E');
insert into detail values(6,2, 'detail values F');
insert into detail values(7,3, 'detail values G');
insert into detail values(8,3, 'detail values H');
insert into detail values(9,3, 'detail values I');

--query the sample data
select m.id MasterId, d.id DetailId, m.info, d.info
from db1.Master m
join db2.Detail d on d.masterid=m.id;

Hope that helps.

Take care

Andreas





Am 09.03.2012 00:06, schrieb Stan Dragnev:
> Sorry for not specifying! It's MySQL databases.
>
> Stan
>
> On 12-03-08 04:38 PM, CptBlaubaer wrote:
>>
>> Hi Stan,
>>
>> what kind of databases do you use? With Oracle you could use database 
>> links, from SQuirreL's point of view you would then query one 
>> database. I don't know about another alternative for your ideas.
>>
>> Perhaps you can provide some more information?
>>
>> Take care
>>
>> Andreas
>>
>> Am 08.03.2012 18:24 schrieb "Stan Dragnev" <sdr...@rn... 
>> <mailto:sdr...@rn...>>:
>>
>>     Hi,
>>
>>     Is it possible to run a query that joins tables across multiple
>>     databases in Squirrel SQL?
>>     E.g.:
>>     select * from db1.table1 inner join db2.table2...
>>
>>     I created a user with permissions for both and connected to the
>>     host but
>>     when trying to query I was getting a no default database selected
>>     error.
>>     Maybe I'm missing something obvious!
>>
>>     Thanks.
>>
>>     Stan
>>
>>     ------------------------------------------------------------------------------
>>     Virtualization & Cloud Management Using Capacity Planning
>>     Cloud computing makes use of virtualization - but cloud computing
>>     also focuses on allowing computing to be delivered as a service.
>>     http://www.accelacomm.com/jaw/sfnl/114/51521223/
>>     _______________________________________________
>>     Squirrel-sql-users mailing list
>>     Squ...@li...
>>     <mailto:Squ...@li...>
>>     https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>>