Nested Join In Excel Vba (adodb) Results In "join Expression Not Supported"

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 express

Solution 1:

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.dateGROUPBY  tb1.date, tb1.longID, tb3.shortID, tb2.fld3, tb1.fld2

Solution 2:

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:

1. 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
   ...
   

2. 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)
   GROUPBY
       x.date, x.longID, x.fld1, x.fld2, tb3.shortID