Combine mirrored correlated subqueries?

[dilettante]

I have a Jet database with an open ADO Connection object [tt]cnCorrDB[/tt]. The database has two tables TABLE-A and TABLE-B that have identical schemas (same number of fields with same types and names).

I need to export all of the records these two tables do not have in common to a text file. "In common" is defined as a record with the same (unique) KEYFIELD value ocurring in each of the two tables.

Right now I have this, which works fine:

With cnCorrDB
  .Execute "SELECT * INTO [Text;Database=" & strFolder & "].[Diffs.txt] " _
         & "FROM [TABLE-A] WHERE NOT EXISTS " _
         & "(SELECT NULL FROM [TABLE-B] WHERE [TABLE-A].KEYFIELD = [TABLE-B].KEYFIELD)", _
           lngExportedDiffsA, adCmdText Or adExecuteNoRecords

  .Execute "INSERT INTO [Text;Database=" & strFolder & "].[Diffs.txt] " _
         & "SELECT * FROM [TABLE-B] WHERE NOT EXISTS " _
         & "(SELECT NULL FROM [TABLE-A] WHERE [TABLE-B].KEYFIELD = [TABLE-A].KEYFIELD)", _
           lngExportedDiffsB, adCmdText Or adExecuteNoRecords
  .Close
End With
lngExportedDiffs = lngExportedDiffsA + lngExportedDiffsB

However I was wondering if the same thing can be done via a single query, e.g. some sort of JOIN. Am I missing the obvious here?

This isn't really a performance issue, as I said it works fine right now. I just hate to leave "poor examples" in my code if I can avoid it.

 

[traingamer]

Use a UNION Query.

Select * from Table-A Where...
UNION
Select * from Table-B where...

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[dilettante]

Sounds good but I can't seem to find a legal syntax that produces the desired result.

 

[PHV]

SELECT * FROM (
SELECT A.* FROM [TABLE-A] A LEFT JOIN [TABLE-B] B ON A.KEYFIELD=B.KEYFIELD WHERE B.KEYFIELD IS NULL
UNION SELECT B.* FROM [TABLE-A] A RIGHT JOIN [TABLE-B] B ON A.KEYFIELD=B.KEYFIELD WHERE A.KEYFIELD IS NULL
) U INTO ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Oops, you wanted a create query, not an append ...
SELECT * INTO ...
FROM (SELECT A.* FROM [TABLE-A] A LEFT JOIN [TABLE-B] B ON A.KEYFIELD=B.KEYFIELD WHERE B.KEYFIELD IS NULL
UNION SELECT B.* FROM [TABLE-A] A RIGHT JOIN [TABLE-B] B ON A.KEYFIELD=B.KEYFIELD WHERE A.KEYFIELD IS NULL
) U

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dilettante]

I'll have to try that, thanks. It looks slower and more memory-intensive that using two queries but that may just be an impression. The MDB isn't enormous but the two tables do have several hundred thousand records each.

 

[PHV]

Anyway, even MS states than DAO is faster than ADO for a Jet Database ...
BTW, did you try my suggestion with outer joins instead of not exists (select ...) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886