How do I nest queries in Excel VBA-CodePudding

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.

CodePudding user response：

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))"

CodePudding user response：

You can execute nested queries with this syntax:

Select * from T1 Where Field01 In (Select Field02 from T2)

If you want compare result of two queries you can do this:

select T1.FieldList, T2.FieldList from T1 FULL OUTER JOIN T2 On T1.Field01 = T2.Field01

then (T1.Field01 is Null) and (T2.Field01 Not is null) ==> All records that exist in T2 but not exist in T1

and (T1.Field01 Not is Null) and (T2.Field01 is null) ==> All records that exist in T1 but not exist in T2

and (T1.Field01 Not is Null) and (T2.Field01 Not is null) ==> All records that exist in in both

I hope it can help.