database links

[Ike]

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

[Erwin Moller]

Re: database links

Ike wrote:
[color=blue]
> 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[/color]

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

[harpreet]

Re: database links

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:[color=blue]
> Ike wrote:
>[color=green]
> > 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[/color]
>
> 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[/color]