Remote SQL databases

[Bob Cole]

I am not a MySQL expert but I have found that it is possible to join different databases, each with various tables. 
The trick is to use the database name to fully qualify the table and item such as:
   SELECT db1.table1.item1, db2.table2.item1 FROM db1.table1, db2.table2 WHERE ...

Here is a sample that I have used successfully:
   put "SELECT  db1.table1.date, " into tSQLcommand
   put "db2.table2.type, FORMAT(db2.table2.amount,2), " after tSQLcommand
   put "db1.table1.code1 " after tSQLcommand
   put "FROM db1.table1 " after tSQLcommand
   put "INNER JOIN db2.table2 ON db1.table1.code1 = db2.table2.code2 " after tSQLcommand
   put "WHERE db2.table2.amount > 1.00 " after tSQLcommand
   put "ORDER BY db1.table1.date; " after tSQLcommand 
   put revDataFromQuery(tab, return, tConId, tSQLcommand) into tItems

I did not find it necessary to give the databases alias names.
In my situation, both databases are on the same machine and can be accessed using the same username/password.
Hope this helps.
Bob


Date: Sun, 23 May 2010 18:19:24 -0300, From: Andre Garzia <andre at andregarzia.com>
Subject: Re: Remote SQL databases

I did it on my 6k thousand table database...

It works fine for us.

I have something like

select Database1.* from DatabaseName1.Table1 as Database1,
DatabaseName2.Table2 ...

On Sun, May 23, 2010 at 4:34 PM, Mark Wieder <mwieder at ahsoftware.net> wrote:

> Bob-
> 
> Sunday, May 23, 2010, 12:04:03 PM, you wrote:
> 
>> Oh hello! You are saying you CAN do a join on a table that is not in
>> the same database?
> 
> Well, I haven't tried it, bu Mark S. implied that it was possible with
> two different aliases...
> 
> --
> -Mark Wieder
> mwieder at ahsoftware.net