CODEWITHSUNDEEP
sqlms-access
MK01111000
MK01111000

Reputation: 832

How do I compare two tables that contain Null values

I am trying to compare two tables from different databases.
I am aware that there are different ways of doing this and the one below may not be the most obvious but there are several things that need to be taken into consideration which would be to cumbersome to discuss here.

The way I'd like to have it done is by concatenating fields and compare the results

SELECT x.* 
FROM     ( SELECT * 
           FROM tblA 'C:\F1\db.mdb'
         ) x      
LEFT JOIN 
        ( SELECT * 
          FROM tblA IN 'C:\F2\db.mdb'
         ) y 
ON (x.fldId = y.fldId) 
AND (x.fldInstrNr = y.fldInstrNr) 
AND (x.fldCode = y.fldCode) 
AND (x.fldQty = y.fldQty) 
AND (x.fldUnit = y.fldUnit) 
AND (x.fldType = y.fldType) 
WHERE (
        ( x.ID & ';' & x.fldId & ';' & x.fldInstrNr & ';' & x.fldCode & ';' & x.fldQty  & ';' & x.fldUnit & ';' & x.fldType & ';' & x.zsfldDemo) <> 
        ( y.ID & ';' & y.fldId & ';' & y.fldInstrNr & ';' & y.fldCode & ';' & y.fldQty & ';' & y.fldUnit & ';' & y.fldType & ';' & y.zsfldDemo))

This works, except when there are null-values in fields.
records with null-values will end up in the result although the record in both database are equal

Is there a way to solve this, and preferably in a way more or less like the above?

Upvotes: 0

Views: 477

Answers (1)

Gustav
Gustav

Reputation: 55906

You can use Nz if not too many records:

ON (Nz(x.fldId) = Nz(y.fldId)) 
AND (Nz(x.fldInstrNr) = Nz(y.fldInstrNr)) 
AND (Nz(x.fldCode) = Nz(y.fldCode)) 
AND (Nz(x.fldQty) = Nz(y.fldQty)) 
AND (Nz(x.fldUnit) = Nz(y.fldUnit)) 
AND (Nz(x.fldType) = Nz(y.fldType))

Upvotes: 1

Related Questions