comparing data in two tables

  • Thread starter Thread starter Jake
  • Start date Start date
J

Jake

I am trying to create a query for a report that has some data in two tables
that match and some that doesn't. I would like to return all the data that
doesn't match from both tables. Inner joins and Outer joins can't do it.
Here is the SQL statement:
SELECT tblRequiredRecordsDND.strSerialNumber,
tblRequiredRecordsDND.strAWM525para,
tblRequiredRecordsDND.memRecommendation, tblRequiredRecordsDND.memRemarks,
tblRequiredRecordsDND.aunAWM525ID, tblRequiredRecordsDND.lngTenCount,
tblRequiredRecordsDND.Subject, tblRequiredRecordsDND.strSubsection,
tblRequiredRecordsDND.strCC130SpecPara,
tblRequiredRecordsDND.strReferenceType,
tblRequiredRecordsDND.strReferenceNumber,
tblRequiredRecordsDND.strReferencePara,
tblRequiredRecordsDND.ysnApplicability, tblRequiredRecordsDND.objFigures,
tblRequiredRecordsDND.strAdvCirc
FROM tblRequiredRecordsDND LEFT JOIN tblRequiredRecords ON
tblRequiredRecordsDND.aunAWM525ID = tblRequiredRecords.aunAWM525ID;
Thanks for help in advance!
Jake
 
Jake said:
I am trying to create a query for a report that has some data in two tables
that match and some that doesn't. I would like to return all the data that
doesn't match from both tables. Inner joins and Outer joins can't do it.
Here is the SQL statement:
SELECT tblRequiredRecordsDND.strSerialNumber,
tblRequiredRecordsDND.strAWM525para,
tblRequiredRecordsDND.memRecommendation, tblRequiredRecordsDND.memRemarks,
tblRequiredRecordsDND.aunAWM525ID, tblRequiredRecordsDND.lngTenCount,
tblRequiredRecordsDND.Subject, tblRequiredRecordsDND.strSubsection,
tblRequiredRecordsDND.strCC130SpecPara,
tblRequiredRecordsDND.strReferenceType,
tblRequiredRecordsDND.strReferenceNumber,
tblRequiredRecordsDND.strReferencePara,
tblRequiredRecordsDND.ysnApplicability, tblRequiredRecordsDND.objFigures,
tblRequiredRecordsDND.strAdvCirc
FROM tblRequiredRecordsDND LEFT JOIN tblRequiredRecords ON
tblRequiredRecordsDND.aunAWM525ID = tblRequiredRecords.aunAWM525ID;
Thanks for help in advance!
Jake
Click to expand...

Do you mean that *BOTH* tables may have rows (data) that are not found in the other table, or do *ONE* of your tables contain all rows found in the other one plus some extras?

In the latter case a left (or right) join may be all you need. If table A contains all rows in table B plus some NOT IN table B, use this (the tables is supposed to have a primary key ID) to find records found only in A:

SELECT A.*
FROM A
LEFT JOIN B
ON A.ID = B.ID
WHERE B.ID Is Null


A UNION query now finds rows the tables *DO NOT* have in common:

SELECT A.*
FROM A
LEFT JOIN B
ON A.ID = B.ID
WHERE B.ID Is Null

UNION
SELECT B.*
FROM B
LEFT JOIN A
ON A.ID = B.ID
WHERE A.ID Is Null

Hth
PerL
 
Back
Top