Join between two databases

[rgbanse]

what is the SQL to do the following:
Database A contains table ItemNbr with fields
Item
Desc

Database B contains table ItemDetail with fields
Item
Plnr
User

I want to join Table A to B linking on Field Item
returning - Item, Desc, Plnr, User

thanks in advance
RGB

 

[olded]

Hi:

If you are on the same server, running dbaccess from database a, adb, the select looks like this:

# tested
select a.item, a.desc, b.plnr, b.user from ItemNbr a, adb:ItemDetail b where a.item = b.item

where adb is the other database. If the database is on a different server:

# untested
select a.item, a.desc, b.plnr, b.user from ItemNbr a, adb@differentserver:ItemDetail b where a.item = b.item

Regards,

Ed

 

[vpshriyan]

Hi Rgb,

If you are on the same server, but have multiple residency i.e., different instances of online database servers have been configured, then the select would look like:

Assuming Databse B is in different instance:

select a.item, a.desc, b.plnr, b.user from ItemNbr a, databaseB@databaseservername:ItemDetail b where a.item = b.item ;

I have tested this statement.

Regards,
Shriyan