joining 2 databases

[dotJoon]

myTable in db1

(id) db1city
(1) Rome 
(2) London 
(5) Paris 
(7) Berlin 

myTable in db2

(id) db2city
(1) Seoul 
(4) Tokyo 
(6) Peking

I have two databases and each database has myTable like the above.


I made the following would-be code for getting my target result below.

would-be code

<cfquery datasource='db1,db2' name='db2'>
select id,city
from db1myTable,db2mytable
order by id
</cfquery>

target result

(1) Rome 
(1) Seoul 
(2) London 
(4) Tokyo
(5) Paris 
(6) Peking
(7) Berlin

The would-be code above doesn't work correctly.
How can I get my target result?

[cfStarlight]

It will most likely involve a UNION or UNION ALL. But the rest depends on your database. Which one are you using?

Some database types allow access to multiple databases by using qualified object names: Caveat: The datasource users must have the appropriate permissions

SELECT t1.Col1, ...
FROM databaseOne.schemaName.TableOne
UNION
SELECT t1.Col1, ...
FROM databaseTwo.schemaName.TableTwo

Failing that, you could query the two tables separately and join the results using a QoQ with a UNION.

[dotJoon]

Which one are you using?

I am using mySQL5.

SELECT t1.Col1, ...
FROM databaseOne.schemaName.TableOne
UNION
SELECT t1.Col1, ...
FROM databaseTwo.schemaName.TableTwo

How can I desinate datasource?

<cfquery datasource='db1,db2' name='db12'>

[cfStarlight]

I don't know if mySQL supports that syntax, but if it does you could probably use either datasource. Assuming the datasource users have the appropriate permissions. (ie The user account must have permissions for the objects in both databases)

Failing that, you could resort to using two separate queries. Then join them together using a QoQ and UNION.

[Aes]

Do you mean you have those tables in two separate MySQL instances? Or are they just in separate databases on the same MySQL instance?

It sounds to me like the latter, in which case Star's syntax should work fine. You should be able to use UNIONs, old-style joins (as below), JOIN ONs, etc. etc. without any problems.

SELECT   tbl1.id, tbl1.city, tbl2.id, tbl2.city
FROM     db1.tbl1, db2.tbl2 ...

[johngag]

you could also accomplish this in a view so then you can call it anytime you need it

it is fair to say that this can be accomplished many different ways