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

Question

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}

score 5 · Accepted Answer · answered Oct 05 '15 at 20:48

5

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.

answered Oct 05 '15 at 20:48

Jonathan Leffler

666,971
126
813
1,185

Thank you! The `:` in between the database and table name on the tertiary table was what I was missing. While I was able to get the query to function, I had to use a `LEFT JOIN`. I see however that the data from the tertiary table is not actually coming back in the result set. In addition, when I try `FULL OUTER JOIN` which _should_ give me everything, the command doesn't seem to ever execute. Give me a moment, I'll post an updated sanitized query. – Andrew Coder Oct 06 '15 at 20:49
You have to specify LEFT or RIGHT (or FULL?) with JOIN, optionally with OUTER squeezed in between. LOJ would produce an empty column if there is no data in TableC that matches the data in TableA on the common column name, and (inner) JOIN would produce nothing. That means that even though you think there are matches between the two databases, the server does not. Can you show a sanitized/simplified form of your data in the three tables — maybe 5 rows in each table with the join column and one other data column, and show a query running against the 3 tables in your example, and the output? – Jonathan Leffler Oct 06 '15 at 20:53
I've added an updated sanitized query to the bottom of my original post. – Andrew Coder Oct 06 '15 at 20:59
You're not selecting any columns from the third table — is that sensible? If so, the content of the WHERE clauses begin to matter; what are you filtering on in the WHERE clause? Presumably, there's a reference to TableC in the WHERE clause if you're not selecting anything from it; otherwise, why bother with an LOJ? – Jonathan Leffler Oct 06 '15 at 21:00
You were right, I forgot to select from the 3rd database in my query. That fixed it! Thank you so much! – Andrew Coder Oct 06 '15 at 21:16

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

1 Answers1