3个表，2个数据库，1个服务器......如何加入？ （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.

我需要制定单个查询来执行以下操作:1)在同一服务器上连接两个(informix)SQL表(已完成/正在工作)2)在同一服务器上加入第三个SQL表,但在另一个数据库中。

For my example code, let's use tableA and tableB on databaseA, and tableC on databaseB.

对于我的示例代码,让我们在databaseA上使用tableA和tableB,在databaseB上使用tableC。

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.

现在,我似乎无法工作的是databaseB上tableC的第二个JOIN子句。我尝试使用数据库名称前缀为所有表/列引用添加前缀,但这似乎不起作用。

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:

只是为了澄清,两个数据库都在同一台服务器上,运行这些命令的用户可以访问这两个数据库。我会提供一条错误消息,但除了字符位置Y附近的第X行(第三个连接子句)上存在错误之外,从Informix回来并没有什么有用的东西。还有一个共同的链接:

databaseB.tableC.columnSHARED

How would/can I join databaseB.tableC to databaseA.tableA and databaseA.tableB?

我如何/可以将databaseB.tableC加入databaseA.tableA和databaseA.tableB?

---

EDIT 2: New Sanitized Query for responder:

编辑2:响应者的新的清理查询:

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}

1 个解决方案

#1

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.

此答案假定数据库具有相同的日志记录模式。如果他们不这样做,则无法进行数据库间连接。