左外连接（三个表之间的连接）？

发布于 2024-07-08 07:29:09 字数 547 浏览 7 评论 0原文

我有一个必须从中获取数据的主表。我有一个左外连接，其中字段的匹配率为 40%。然后我有另一个连接，我需要将表 A 中的数据与之匹配。

这是伪代码形式的 SQL。该查询将不起作用。

-- 这是我想做但行不通的部分。 AND H.COL3 = A.STATE????

我正在使用 IBM DB2。

SELECT DISTINCT
  APP_NO as app_no,
  A.STATE as state
  ...
  ... Fields
  ...
FROM 
  TABLE_A A
LEFT OUTER JOIN  
  TABLE_B HIST
ON
  HIST.COL1 = A.COL1
, TABLE_C B  
LEFT OUTER JOIN
  TABLE_D H  
ON
  H.COL2 = B.COL2
-- This is the part I want to do but doesn't work.
AND
  H.COL3 = A.STATE????
WHERE
  A.BRANCH = 'Data'

原文

I have a main table that I must get data from. I have a left outer join where the fields will match 40% of the time. And then I have another join where I need to match the data from table A with.

This is the SQL in pseudo code. This query won't work.

-- This is the part I want to do but doesn't work.
AND H.COL3 = A.STATE????

I am working with IBM DB2.

SELECT DISTINCT
  APP_NO as app_no,
  A.STATE as state
  ...
  ... Fields
  ...
FROM 
  TABLE_A A
LEFT OUTER JOIN  
  TABLE_B HIST
ON
  HIST.COL1 = A.COL1
, TABLE_C B  
LEFT OUTER JOIN
  TABLE_D H  
ON
  H.COL2 = B.COL2
-- This is the part I want to do but doesn't work.
AND
  H.COL3 = A.STATE????
WHERE
  A.BRANCH = 'Data'

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情仇皆在手 2024-07-15 07:29:09

我认为您可以像这样重写它（但我可能会错误地阅读您的语句）

FROM 
  TABLE_A A LEFT OUTER JOIN TABLE_B HIST ON
      HIST.COL1 = A.COL1
  LEFT OUTER JOIN TABLE_D H ON 
      H.COL3 = A.STATE
  LEFT OUTER JOIN TABLE_C B ON H.COL2 = B.COL2
WHERE
  A.BRANCH = 'Data'

此外，有关此错误的 IBM doco 指出：

与 JOIN 运算符或 MERGE 语句关联的 ON 子句无效。
说明：

ON 子句中的列引用必须仅引用 ON 子句范围内的表的列。

所以我可能会弄错，它看起来就像外部连接“H”时的错误 ON 子句.COL3 = A.STATE" 超出了 On 子句的范围，因为表 A 不在该范围内。

I think you could re-write it like this (but I could be reading your statement wrong)

FROM 
  TABLE_A A LEFT OUTER JOIN TABLE_B HIST ON
      HIST.COL1 = A.COL1
  LEFT OUTER JOIN TABLE_D H ON 
      H.COL3 = A.STATE
  LEFT OUTER JOIN TABLE_C B ON H.COL2 = B.COL2
WHERE
  A.BRANCH = 'Data'

Also, the IBM doco on this error states:

An ON clause associated with a JOIN operator or in a MERGE statement is not valid.
Explanation:

Column references in an ON clause must only reference columns of tables that are in the scope of the ON clause.

So I could be mistaken, it just looks like the erronous ON clause when outer joining "H.COL3 = A.STATE" is out of scope of the On clause because the table A is not in that scope.

回复收藏 0 原文