query two databases

[thompom]

hi,

how do i query two identical tables in different DBs - the tables are not joined - i want to merge the results

SELECT field1, field2, field3
FROM db1.table, db2.table

with this i get ambiguous field errors
[hope im not being dumb here]

 

[vacunita]

Assuming both DB's are in the same server, and the user doing the querying has access rights to both DB's.


Select databasename1.tablename.fieldname1, databasename2.tablename.fieldname FROM databasename1.tablename, databasename2.tablename WHERE databasename2.tablename.fieldnamex=databasename2.tablename.fieldnamey


----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[DonQuichote]

What do you mean by "merged"? If you want to select them as if they were in one table, you can use a union query:

SELECT field1, field2, field3 FROM db1.table
UNION
SELECT field1, field2, field3 FROM db2.table

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[thompom]

thanks DonQuichote - just what i need.