3 Tables, 2 Databases, 1 Server... How to Join? (SQL/Informix)
Assuming the current database is the one that holds the two tables, then you could write:
SELECT A.ColumnA, B.ColumnB, C.ColumnC FROM tableA AS A JOIN tableB AS B ON B.columnSHARED = A.columnSHARED JOIN databaseB:tableC AS C ON C.columnSHARED = A.ColumnSHARED WHERE ([…where clauses are inconsequential for this…])The full notation for a table name is:
[database[@server]:][owner.]tablenameConsequently, you could also write:
SELECT A.ColumnA, B.ColumnB, C.ColumnC FROM databaseA:tableA AS A JOIN databaseB:tableB AS B ON B.columnSHARED = A.columnSHARED JOIN databaseB:tableC AS C ON C.columnSHARED = A.ColumnSHARED WHERE ([…where clauses are inconsequential for this…])This would work correctly in the current server, regardless of which database is the current database.
This answer assumes that the databases have the same logging mode. If they don't, you can't do the inter-database join.