How do I nest queries in Excel VBA

February 14, 2022

I am not sure if it is possible, but I am trying to nest queries.
First I create the following 2 queries:

MyQuery = "Select * from " & "T1"
MyQuery2 = "Select * from " & "T2"

Then I’d like to compare both queries and return the differences in the following way:

mkQry = "SELECT (" & MyQuery & ").*" _
                & "FROM (" & MyQuery & ") LEFT JOIN (" & MyQuery2 & ") ON " _
                & "(" & MyQuery & ".F1) = " & MyQuery2 & ".F1) AND " _
                & "(" & MyQuery & ".F2 =  " & MyQuery2 & ".F2) AND " _
                & "(" & MyQuery & ".F3 =  " & MyQuery2 & ".F3) AND " _
                & "(" & MyQuery & ".F4 =  " & MyQuery2 & ".F4) AND " _
                & "(" & MyQuery & ".F5 =  " & MyQuery2 & ".F5)" _
                & "WHERE (((" & MyQuery2 & ".F5) Is Null))"
                
        Set MyRecordset99 = MyConnection2.Execute(mkQry)
        Worksheets("TST").Range("A1").CopyFromRecordset MyRecordset99

However, mkQry contains a syntax error and I am not sure how to solve this.

My question is:
Is it possible to nest queries, more or less, in the way I am trying to do and if so, how can I update the syntax to make it work.

>Solution :

You should alias each of the sub-queries, then use the aliases throughout.

mkQry = "SELECT x.* " _
                & "FROM (" & MyQuery & ") x LEFT JOIN (" & MyQuery2 & ") y ON " _
                & "(x.F1 = y.F1) AND " _
                ...
                & "(x.F5 = y.F5) " _
                & "WHERE (((y.F5) Is Null))"