Remote SQL databases

[Bob Sneidar]

All well and good... so long as the database DOING the JOIN knows about the database it is joining to. In my case, this is not so. The joining database has no access to the related database. Thanks for the help tho. It's still an education. 

Bob


On May 24, 2010, at 9:00 PM, Bob Cole wrote:

> 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
> 
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution