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
