Thread: [Squirrel-sql-users] joining tables across multiple databases
A Java SQL client for any JDBC compliant database
Brought to you by:
colbell,
gerdwagner
From: Stan D. <sdr...@rn...> - 2012-03-08 17:23:42
|
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 |
From: CptBlaubaer <cpt...@gm...> - 2012-03-08 21:38:31
|
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...>: > 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... > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users > |
From: Stan D. <sdr...@rn...> - 2012-03-08 23:06:12
|
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...>: 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... https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users |
From: Andreas O. <cpt...@gm...> - 2012-03-09 01:23:40
|
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 >> |
From: Stan D. <sdr...@rn...> - 2012-03-09 13:47:47
|
Hi Andreas, That's what I thought I was trying, but I gave it another go today and had no problems querying. Other than the autocomplete not finding the names for tables and fields, everything went fine. Clearly it was user error! Thanks everyone for trying to help. Stan On 12-03-08 08:23 PM, Andreas Oswald wrote: 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...>: 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... https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users |