SQL Text Field Comparison

[gray]

Hi

I am comparing the columns in two tables in a JOIN to see if there are differences. The columns, COL3 and COL4, are "Text" fields. They are declared as:

Field Size = 255
Default Value = "" (an empty string)
Required = No
Allow Zero Length = Yes
Indexed = No

I use the SQL comparator <> in my test... so ...

SELECT * FROM 
Table_A AS Tgt  
LEFT JOIN Table_B AS Src  
ON (Src.Col1 = Tgt.Col1 AND Src.Col2 = Tgt.Col2)
WHERE Src.Col3 <> Tgt.Col3 OR Src.Col4 <> Tgt.Col4

I expected that where Src.Col3 was set to 0 (the string character zero) and Tgt.Col3 was set to the default of "" (a zero length string) that the query would return that row? It does not however? No doubt this is tied in with the old NULL string and Empty string chestnut?

What is the best way to compare "text" fields in Access please?

 

[CJ_London]

"0" is not the same as "".

Also "" is not the same as null

So the problem is not with Null and empty strings but the way you make your comparison.

suggest you try

Src.Col3 <> iif(Tgt.Col3="","0",Tgt.Col3)

or

iif(Src.Col3="0","",Src.Col3)=Tgt.Col3

 

[spikepl]

You've seemingly made a bunch of assumptions about how left joins produce "the missing fields", which I think are incorrect - you probably get Null and not the default. I'd suggest you try with a more basic two-column example to convince yourself.

 

[gray]

Hi

Thanks for the replies....

'"0" is not the same as "".'

Sorry CJ, I probably used a bad example there... a better explanation would have been if I had said ....

Src.Col3 was set to any character (beit 0,999,ABC or whatever) and Tgt.Col3 was set to the default of "" it does not return the row.

assumptions about how left joins produce "the missing fields"

Hmmm... interesting... so if I understand what you are saying the JOIN sometimes might return NULLs in cols3 and 4 where they have "" values? So I guess there is a better comparison than just <> ... i.e. where NULLS are catered for ?

Would that be

WHERE Nz(Src.Col3) <> Nz(Tgt.Col3) OR Nz(Src.Col4) <> Nz(Tgt.Col4)?

??

 

[CJ_London]

Hmmm... interesting... so if I understand what you are saying the JOIN sometimes might return NULLs in cols3 and 4 where they have "" values? So I guess there is a better comparison than just <> ... i.e. where NULLS are catered for ?

No. Left joins will (in your example) return all records in table A and only those where they exist in table B. So if there is no matching record in table B then all the table B values will be null

Inner joins only return records where there is a matching record between table A and table B

 

[gray]

Ah yes, I see..... Thanks...

What I am ultimately trying to do is sync my target table (A) with my source table (B).... So I had something like this......

UPDATE
Table_A AS Tgt  
LEFT JOIN Table_B AS Src  
ON (Src.Col1 = Tgt.Col1 AND Src.Col2 = Tgt.Col2)
SET.Tgt.Col3=Src.Col3, Tgt.Col4=Src.Col4
WHERE Src.Col3 <> Tgt.Col3 OR Src.Col4 <> Tgt.Col4

So that when any values change in Col3 or Col4 in the source table I could update those cols in the target table...

I fear I am barking up the wrong tree?

 

[CJ_London]

you are.

It is not clear what you are trying to achieve. To get the correct sql phrasing, why don't you use the query builder and then look at the sql it generates to get a better understanding.

Also suggest you research the use of isNull, is null (they are different), is not null and the nz function

 

[gray]

Thanks for pushing me in the right direction…

I actually found this handy tip for dealing with comparisons where NULLs might be lurking… in my case I amended my query as follows:-

SELECT * FROM 
Table_A AS Tgt  
LEFT JOIN Table_B AS Src  
ON (Src.Col1 = Tgt.Col1 AND Src.Col2 = Tgt.Col2)
WHERE (Src.Col3 & "" <> Tgt.Col3 & "") OR (Src.Col4 & "" <> Tgt.Col4 & "")

From this thread:-

http://stackoverflow.com/questions/12147894/sql-query-with-null-value-comparison-in-ms-access

It seems to have done the trick!

Thanks again

 

[CJ_London]

it will do until you have a record in tgt which does not have a matching record in src