>>Obviously I am being dense today, but I still cannot see why changing the order of the 2 expressions changes the results.
>>
>>
select ... where (a.fld2a=b.fld2b) AND (trim(a.fld1a)=trim(b.fld1b))
>>
>>and
>>
>>
select ... where (trim(a.fld1a)=trim(b.fld1b)) AND (a.fld2a=b.fld2b)
>>
>>really ought to give the same results & the results should be consistent regardless of setting ansi (I know setting it to on affects the results & leads to consistent results, but even if off the results should be consistent).
>>
>>I described the problem somewhere as asking for a list of all people called Len and having fair & then asking for a list of all people with fair hair and called Len. Would you (intuitively) expect to get different results ?
>
>Len,
>Sorry but caught off guard :) I surrender.
>Cetin
Wasn't trying to catch you out, I just thought I was missing something. It does seem to be a possibly severe problem if the order of the expressions can have such an effect.
I originally found the problem in Foxpro 2.5 & tried contacting Microsoft support - I used the above description of name/hair colour & was told by the support person that I obviously knew nothing of SQL & of course I should expect different results (this was also the same person who told me to remove the brackets/parentheses to see if it gave the correct results - I've just mentioned this on another thread).
Len Speed