Posted by harpreet on 06/02/06 16:43

Usually I do it like this If I understand you question correctly...

select a.account, a.customer;
b.invoice ;
from db1.tbl1 a, db2.tbl2 b ;
where a.account = b.account

Erwin Moller wrote:
> Ike wrote:
>
> > Can someone show me how I might be able to connect to two separate
> > databases, and create a query from the two? I understand in Oracle this is
> > referred to as "database links" but is there a more general way to do this
> > (i.e. will work with, say, mysql and other DBs?) I want to comapre a field
> > that is present in two separate files on two seperat databases. Thanks,
> > Ike
>
> Hi Ike,
>
> AFAIK this cannot be done in a general reliable way.
> Of course, you can always make 2 connections, pull in all the data you need,
> and join/filter/etc the tables/results yourself in PHP (or whatever it is
> your SQL-query should be doing).
>
> Some databases however offer the possibility to make queries that span more
> databases of the same kind, eg Oracle or Postgres. I think M$ Access can do
> it too.
>
> Maybe there are packages out that facilitate multiple different database
> queries, I don't know them.
> Possibly, if you make 2 ODBC-connections you can use them both. (not sure
> either)
>
> Whatever solution you find, I expect that the performance of such queries
> will be low, simply because the data has to be gathered from different
> places and cannot be run in one place unless all the data is pulled in,
> which is also a lot of overhead.
>
> I would approach this as I described above, just make 2 or more connections,
> get in the data you need, and optimize where you can. At least you know
> excactly what is going on in that way and you can use your knowledge of the
> systems to optimize (eg, not pulling in all the data from all related
> tables).
>
> just my 2 cent.
>
> Good luck.
> Regards,
> Erwin Moller

[Back to original message]