sqldatabasejoininformixisql

3 Tables, 2 Databases, 1 Server... How to Join? (SQL/Informix)


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}

Solution

  • 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.