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
> ----
>
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
