Nested Join in Excel VBA (ADODB) Results In "JOIN expression not supported"

Question

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

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.

Any help would be greatly appreciated!

Thanks.

D-Shih · Accepted Answer · 2018-08-02 20:33:12Z

3

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

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

edited Aug 2, 2018 at 20:33

answered Aug 2, 2018 at 20:26

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Olivier Jacot-Descombes · Accepted Answer · 2018-08-02 20:42:56Z

The purpose of the ON-clause is to join 2 tables, but you try to join 3 tables at the same time with ON (tb3.shortID = tb2.shortID AND tb1.date = tb3.date). You can solve this problem in 2 ways:

Move a part of the ON to the WHERE clause so that only 2 tables are involved.

...
FROM      ([Table1$] AS tb1
           LEFT JOIN [Table2$] AS tb2
               ON tb1.longID = tb2.longID)
          LEFT JOIN [Table3$] AS tb3
              ON tb2.shortID = tb3.shortID
WHERE tb1.date = tb3.date
...

Use a sub-query

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

Collectives™ on Stack Overflow

Nested Join in Excel VBA (ADODB) Results In "JOIN expression not supported"

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related