I need to formulate a single query to do the following: 1) Join two (informix) SQL tables on the same server(already done/working) 2) Join a third SQL table on the same server, but in a different database.
For my example code, let's use tableA and tableB on databaseA, and tableC on databaseB.
Joining the two tables on the same database is no problem.
SELECT tableA.columnA
tableB.columnA
FROM
tableA
JOIN
tableB
ON
tableB.columnSHARED = tableA.columnSHARED
WHERE
([where clauses are inconsequential for this])
Now, what I can't seem to get working is a second JOIN clause for tableC on databaseB. I have tried prefixing all table/column references with database name prefixes, but this doesn't seem to work.
Just to clarify, both databases are on the same server and the user running these commands has access to both databases. I would provide an error message, but there isn't really anything useful coming back from Informix besides the fact that there is an error on line X near character position Y (the third join clause). There is also a common link:
databaseB.tableC.columnSHARED
How would/can I join databaseB.tableC
to databaseA.tableA
and databaseA.tableB
?
EDIT 2: New Sanitized Query for responder:
SELECT FIRST 100
tableA.sharedColumn,
tableA.colA,
tableA.colB,
tableA.colC,
tableA.colD,
tableA.colE,
tableA.colF,
tableA.colG,
tableB.colA ,
databaseB:tableC.column
FROM
tableA
JOIN
tableB
ON
tableB.sharedColumn = tableA.sharedColumn
LEFT OUTER JOIN
databaseB:tableC
ON
databaseB:tableC.sharedColumn = databaseA:tableA.sharedColumn
WHERE
{where clauses}
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.]tablename
Consequently, 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.