Comparing tables for non matches

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I started out with two tables imported from a peoplesoft database. The tables
contain four fields that need to be checked for matches. I created a
make-table query that found the matches by putting in statement that read
like: match field 1 and match field 2 and match field 3 and match field 4.
This worked great! Then I needed to get a list of the non-matches. This
proved to be difficult. First I tried to create another make-table query that
did: if table 1.field 1 <> table 2.field 1 OR table 1.field 2 <> table
2.field2 OR table1.field3 <> table2.field3 OR table1.field4 <> table2.field4.
That overloaded my virtual memory. I tied a few other queries that either
gave bad results or the same Temp memory error. What is needed is to look at
a single row of info in table 1 and then look at each row in table 2 and see
if all four fields match. Then some way of indicating if the row matches or
not. It would then need to get the next row from table1 and compare with all
the rows in table 2 again, so that I would end up knowing if every row in
table 2 is a match or not.
 
Try a query similar to this to show you the duplicates in Table2 for a
record in Table1:

SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2
ON Table1.Field1 = Table2.Field1 AND
Table1.Field2 = Table2.Field2 AND
Table1.Field3 = Table2.Field3 AND
Table1.Field4 = Table2.Field4;
 
Just add a criterion to find the nonmatches:

SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2
ON Table1.Field1 = Table2.Field1 AND
Table1.Field2 = Table2.Field2 AND
Table1.Field3 = Table2.Field3 AND
Table1.Field4 = Table2.Field4
WHERE Table2.Field1 Is Null;
 
This was very helpful. I actually used an OUTER JOIN to get all the records
from table 2 and then used IS NULL in the table1 fields. The only problem I
have now is that for some unknown reason I am getting 23 more records (out of
149,228) then what was in the original table 2 to start with!?
 
My error... the INNER JOIN should have been replaced by a LEFT JOIN, for the
example that I posted.

Not having any idea of the data that are in the tables, it's hard to give a
reasonable suggestion for the "duplication" of records (and I don't know if
the duplication is from the INNER JOIN query or from the outer join query?).
 
Back
Top