How can I join 2 tables from 2 diff. hosts?

Anon

Hi!
I accidentally posted this in the wrong forum - so here it is again:
I am trying to JOIN two tables from two different databases on two different hosts, but have no clue on how to connect them or about the statement, because I would have to connect to two different databases first and the examples I saw in the manual don\'t seem to cover this issue.
So, here is what I have:
DB1 with the table zip
It has the fields:
- id (unique)
- zip
- city
- latitude
- longitude

DB2 with the table userdata
It also has the field \"id\", but it is not unique, because there can be many users from the same place.
- username

I am doing a query on DB1 first to limit the results. Then I would need to have all corresponding usernames of the table userdata of DB2,but I don\'t know how to do this.
Any help would be greatly appreciated! 🙂
Ciao
Sascha

Anon

Ok, I haven't solved my problem yet, but I think I'm close to it. Maybe someone can give me the final hint? 🙂 I have two queries that are working, but when I try to combine them it won't work.
I have one query that is working fine to limit the results:
SELECT , ($d) AS distance FROM zip HAVING distance < 25 order by distance
The second query is:
SELECT FROM zip,userdata WHERE zip.id = userdata.id
How can I combine them?
I tried:
SELECT *,($d) AS distance FROM zip,userdata HAVING distance < 25 WHERE zip.id = userdata.id order by distance
But I only get "0 is not a MySQL result index".

Anon

First of all if they are 2 different databases on 2 different hosts you can't combine the SQL. You would have to do 2 seperate querys and combine the results yourself.

To do 2 different queries like that you will have to create 2 connections, one to each database.

Anon

Which RDBMS are you using? I've done something similar using Oracle (7.3.4 - I assume it applies to any version > as well).

SQL> create database link grok
connect to dbid identified by dbpass
using 'dbname in tnsnames.ora';
SQL> database link created

You can now tie in the 'external' database this way:

Select db_field1,
db_field2@grok
from localtable, externaltable@grok
where db_field1 = 'somecriteria'
and db_field2@grok = 'somecriteria'
/