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 --------------------------------------------------------------------- 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
