List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:April 5 2002 11:49pm
Subject:Re: Cross-database joins
View as plain text  
It's perfectly possible to join tables from different databases, as long
as the databases are managed by the same server.  Just qualify your
table names with a leading database name.

For example, a join between two tables in the same database might
be written like this:

SELECT t1.col1, t2.col2
FROM t1, t2
WHERE t1.id1 = t2.id2;

If instead t1 is in db1 and t2 is in db2, just write it like this:

SELECT db1.t1.col1, db2.t2.col2
FROM db1.t1, db2.t2
WHERE db1.t1.id1 = db2.t2.id2;


At 15:42 -0800 4/5/02, Richard wrote:
>You are talking about "heterogeneous queries".  I believe it is Delphi which
>achieves this feature for you, not the databases themselves.  Here 
>is a quote from
>the Delphi help system:
>
>"Delphi supports heterogeneous queries, that is, queries made 
>against tables in
>more than one database. A heterogeneous query may join tables on different
>servers, and even different types of servers. For example, a 
>heterogeneous query
>might involve a table in a Oracle database, a table in a Sybase 
>database, and a
>local dBASE table."
>
>Delphi parses the SQL code, sends the appropriate query to each 
>database, and then
>assembles the result sets locally. The syntax example they provide 
>is as follows:
>
>SELECT Customer.CustNo, Orders.OrderNo
>FROM ":Oracle1:CUSTOMER"
>   JOIN ":Sybase1:ORDERS"
>     ON (Customer.CustNo = Orders.CustNo)
>WHERE (Customer.CustNo = 1503)
>
>The database names such as Sybase1 would be ODBC DSN or BDE (Borland Database
>Engine) database alias names, or database object (TDatabase) names.
>
>It sounds to me like it could be terribly inefficient depending on 
>your query and
>whether or not Delphi queries the multiple databases concurrently or 
>sequentially.
>However, I suggest you just try it if you can, and let us know what you find.
>
>--Richard
>
>
>>  Can you please tell me, is it possible to do queries that join 
>>MySql tables that
>>  are in different databases?
>>
>>  I have a couple of Delphi database programs that use Paradox tables.  I'd
>>  like to move them over to something else that is faster.  These 
>>programs are set
>>  up so that they have a shared database on our server, and then a 
>>local database
>>  on each machine.  I have queries that have to be able to join tables in the
>>  local and the server db's.  I tried this with Interbase, and it 
>>doesn't support
>>  cross-database joins.  I'm wondering if MySql can do this?
>>
>>  If so, how would you modify the below query to show the different databases?
>>  Specifically, how would you specify the database that the table is in?
>>
>>  SELECT D.BkID, D.Chap, D.Vrs, D.RTypID
>>  FROM ":OntData:ScrpUF.DB" D
>>  INNER JOIN ":OntLclData:LclSrchBkParam.DB" d1
>>  ON (d1.BkID = D.BkID)
>>  WHERE D.ScrpUF LIKE "%search text%"
>>
>>  *** If this is not possible, can this be accomplished with a UNION by
>>  selecting all the records in the local table, and joining the result set
>>  with the shared table?  If so, how?
>>
>>  Any help will be *greatly, greatly* appreciated.
>>
>>  In Christ,
>>
>>  Peter, hierodeacon
>>
>>  ----
>>  Dormition Skete
>>  A Monastery of the Russian Orthodox Autonomous Church
>>  http://www.BuenaVistaCO.com/GOC
>  > http://www.RussianOrthodoxAutonomousChurchInAmerica.com

Thread
Cross-database joinsDormition Skete5 Apr
  • Re: Cross-database joinsRichard6 Apr
    • Re: Cross-database joinsPaul DuBois6 Apr
      • Re: Cross-database joinsRichard6 Apr
Re: Cross-database joinsDormition Skete6 Apr