Comparing tables with Null values

[mk01111000]

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 with 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?




I tried the following but this takes very long to run (There are over 1000 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))

[June7]

If the records are 'equal' then the WHERE clause should exclude them, even if they have null.

Are you sure they are null in both? An empty string is not same as null.

[ssanfu]

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.zsfldDemo) <> ( y.ID & ';' & y.zsfldDemo)

Why wouldn't the WHERE clause be the bold line above?

x.fldCode value	y.fldCode value	result	record included in result set
Null	Null	False	No
Null	has value	False	No
has value	Null	False	No
"Sue"	"John"	False	No
"Sue"	"Sue"	TRUE	Yes
has value	zls	False	No

So the other 5 fields in the join would be the same results.



Due to the join clause, you know the 6 joining fields are equal for the record from "table x" to be included in the result set.
So the only difference could be the the two fields that are not in the join clause - "ID" and "zsfldDemo"??


Am I in bizzaro world?

[CJ_London]

I am trying to compare two tables from different databases.

with what aim? what result do you want? return records in db x that don't match in db y (which is what you are trying to do)? the other way round? both? what are you going to do with the information once you have it?

your code as presented will fail due to typo's. Which implies you have free typed it rather than use copy/paste. Aside from the fact that free typing takes longer, it gives me no confidence we are even looking at the real problem

there are several things that need to be taken into consideration which would be to cumbersome to discuss here.

if we knew those considerations, we might be able to suggest a better way.

but that aside could perhaps make it simpler although it would still negate the use of indexes

SELECT x.* 
FROM     ( SELECT *,  ID & ';' & fldId & ';' & fldInstrNr & ';' & fldCode & ';' & fldQty  & ';' & fldUnit & ';' & fldType & ';' & zsfldDemo as compare

           FROM tblA 'C:\F1\db.mdb'
         ) x      
LEFT JOIN 
        ( SELECT * ,  ID & ';' & fldId & ';' & fldInstrNr & ';' & fldCode & ';' & fldQty  & ';' & fldUnit & ';' & fldType & ';' &  as compare

          FROM tblA IN 'C:\F2\db.mdb'
         ) y 
ON (x.compare = y.compare)

If this is a regular occurrence I would use a hash function on all the relevant fields and compare on that - probably only going to compare 8 or 10 chars rather the larger number you have at the moment. Perhaps even store the hash value in an additional indexed field.

Another way to try using a non standard join (so can only be done in the sql window)

SELECT x.* 
FROM     ( SELECT * 
           FROM tblA 'C:\F1\db.mdb'
         ) x      
LEFT JOIN 
        ( SELECT * 
          FROM tblA IN 'C:\F2\db.mdb'
         ) y 
ON (x.id<>y.id)
AND (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) #
AND (x.zsfldDemo<>y.zsfldDemo)

other comment: your nz function should be a bit faster if you provide a default value should you choose to try that way again


(Nz(x.fldId,0) = Nz(y.fldId,0))

use 0 for numbers and '' for strings