>Some of this has been posted as an answer to another thread, but I'm posting this as a separate question.
>
>Using VfP6 (no SP yet - waiting for SP5) + Windows 98.
>
>I have two tables, identical structure - different field names, ascending indexes on both fields in each (problem occurs without indexes too).
>
>

>Table 1 :
>fld1a Char 10
>fld2a Numeric 5 0
>2 records
>fld1a   fld2a
>ABCD    1
>ABC     2
>
>Table 2 :
>fld1b Char 10
>fld2b Numeric 5 0
>2 records
>fld1b   fld2b
>ABC     1
>ABC     2
>

>
>Can anyone explain why the following queries give different results :
>
>Query 1 :
>

>select * from db1 a, db2 b where (a.fld2a=b.fld2b) AND (trim(a.fld1a)=trim(b.fld2b))
>

>
>Query 2 :
>

>select * from db1 a, db2 b where (a.fld2a=b.fld2b) AND (trim(a.fld1a)=trim(b.fld2b)) AND (a.fld2a=b.fld2b)
>

>
>Query 3 :
>

>select * from db1 a, db2 b where (trim(a.fld1a)=trim(b.fld2b)) AND (a.fld2a=b.fld2b)
>

>
>Query 2 differs from query 1 by duplicating the check for a.fld2a=b.fld2b
>Query 3 differs from query 1 by swapping the order of the query.
>
>Am I missing something fundamental ?

Len,
Sorry for the jump after months :) 'What's bad about foxpro' thread leaded me here.
I'm not an SQL expert but I think FP was doing it right.
This is from VFP help (same in VFP5-7) :

'String Order In SQL commands, the left-to-right order of the two strings in a comparison is irrelevant—switching a string from one side of the = or == operator to the other doesn’t affect the result of the comparison.'

In this manner I expected trimmed 'ABCD' # 'ABC' ('ABC' # 'ABCD').
Thinking trimming would have no effect adding 'set ansi' or removing trim() gives the same result in all 3 queries.

Trimming + like operator somewhat returns result sets you want but of course not if longer string is in second db.
I had this quick&dirty workaround, not smart but works and could drop UDF integrating 'like' into SQL :

select * from db1 a, db2 b ;
  where (a.fld2a=b.fld2b) AND myequal(db2.fld1b,db1.fld1a)

select * from db1 a, db2 b ;
  where (a.fld2a=b.fld2b) ;
   AND myequal(db2.fld1b,db1.fld1a) ;
   AND (a.fld2a=b.fld2b)

select * from db1 a, db2 b ;
  where myequal(db2.fld1b,db1.fld1a) ;
  AND (a.fld2a=b.fld2b)

function myequal
lparameters tcStr1, tcStr2
return trim(tcStr1)=trim(tcStr2) or trim(tcStr2)=trim(tcStr1)

PS: Swapped order of fld1a, fld1b just to verify longer string place has no effect.
Cetin