Excel VBA(ADODB)中的嵌套联接结果“不支持联接表达式" [英] Nested Join in Excel VBA (ADODB) Results In "JOIN expression not supported"

问题描述

我有一系列三个表，我想在Excel VBA应用程序中使用ADODB将它们连接在一起.我正在使用以下查询，这会导致不支持JOIN表达式"错误:

I have a series of three tables which I would like to join together using ADODB in an Excel VBA application. I am using the following query, which is resulting in the "JOIN expression not supported" error:

SELECT    tb1.date, 
          tb1.longID, 
          tb1.fld1,
          tb2.fld2,
          tb3.shortID,
          SUM(tb1.fld3) AS three, 
          SUM(tb1.fld4) AS four, 
          SUM(tb3.fld5) AS five
FROM      ([Table1$] AS tb1 LEFT JOIN [Table2$] AS tb2 ON tb1.longID = tb2.longID)
LEFT JOIN [Table3$]  AS tb3
ON        (tb3.shortID = tb2.shortID AND tb1.date = tb3.date)
GROUP BY  tb1.date, tb1.longID, tb3.shortID, tb2.fld3, tb1.fld2

如果我要省略shortID列对，则查询工作正常.如果我省略date列对，则查询工作正常.但是，当我将两者结合起来时，就遇到了问题.

If I were to omit the shortID column pair, the query works fine. If I omit the date column pair, the query works fine. But as soon as I combine the two, that's when I run into issues.

任何帮助将不胜感激！

谢谢.

推荐答案

尝试使查询的ON部分内的所有内容都放在括号内.

Try to let everything inside the ON part of the query to be inside parenthesis.

JOIN操作中的ON语句不完整或包含太多表.您可能需要将ON表达式放在WHERE子句中.

The ON statement in your JOIN operation is incomplete or contains too many tables. You may want to put your ON expression in a WHERE clause.

SELECT    tb1.date, 
          tb1.longID, 
          tb1.fld1,
          tb2.fld2,
          tb3.shortID,
          SUM(tb1.fld3) AS three, 
          SUM(tb1.fld4) AS four, 
          SUM(tb3.fld5) AS five
FROM      
[Table1$] AS tb1 
LEFT JOIN [Table2$] AS tb2 ON (tb1.longID = tb2.longID)
LEFT JOIN [Table3$]  AS tb3 ON (tb3.shortID = tb2.shortID)
WHERE tb1.date = tb3.date
GROUP BY  tb1.date, tb1.longID, tb3.shortID, tb2.fld3, tb1.fld2

这篇关于Excel VBA(ADODB)中的嵌套联接结果“不支持联接表达式"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！