My apologies to those who have already read this, and my thanks to Tamar for her responses in another thread. I thought I would repost this in a more appropriate category & hope some more people will look at it & give me more advice.

Since Foxpro 2.6 (DOS & Windows), I have found some oddities in a very simple VfP select statement, involving a join between two tables & a filter condition.

I get different results from seemingly identical queries, depending on whether the tables are joined first. In some cases the order of expesssions in the select statement can make a difference to the result set, even though it logically should not (examples 2 & 3) in particular.

Below is the code used to generate the results. The table structures & field names have been arranged to simplify the queries. The problems occur regardless of whether or not the tables are indexed, so I haven't indexed. In the original tables, the fld1b & fld2b fields were key fields on which the join was made.

In each instance, I would expect goodtbl{n} & badtbl{n} to contain the same results.

I would expect examples 2 & 3 should give identical results, the only difference being the swapping of the numeric comparisons in the query. I would also expect examples 4 & 5 should give identical results, the only difference being a duplicate join expression.

set safety off
create table tbla (fld1a C(10), fld2a N(5,0))
insert into tbla values ("ABCD", 1)
insert into tbla values ("ABC", 2)
use
create table tblb (fld1b C(10), fld2b N(5,0))
insert into tblb values ("ABC", 1)
insert into tblb values ("ABC", 2)
use

select * from tbla, tblb into cursor tblc

select * from tblc where (trim(fld1a)=trim(fld1b)) into cursor goodtbl1
select * from tbla, tblb where (trim(fld1a)=trim(fld1b)) into cursor badtbl1

select * from tblc where (fld2a=2) AND (trim(fld1a)=trim(fld1b)) AND (fld2a=fld2b) into cursor goodtbl2
select * from tbla, tblb where (fld2a=2) AND (trim(fld1a)=trim(fld1b)) AND (fld2a=fld2b) into cursor badtbl2

select * from tblc where (fld2a=fld2b) AND (trim(fld1a)=trim(fld1b)) AND (fld2a=2) into cursor goodtbl3
select * from tbla, tblb where (fld2a=fld2b) AND (trim(fld1a)=trim(fld1b)) AND (fld2a=2) into cursor badtbl3

select * from tblc where (fld2a=fld2b) AND (trim(fld1a)=trim(fld1b)) AND (fld2a=fld2b) into cursor goodtbl4
select * from tbla, tblb where (fld2a=fld2b) AND (trim(fld1a)=trim(fld1b)) AND (fld2a=fld2b) into cursor badtbl4

select * from tblc where (fld2a=fld2b) AND (trim(fld1a)=trim(fld1b)) into cursor goodtbl5
select * from tbla, tblb where (fld2a=fld2b) AND (trim(fld1a)=trim(fld1b)) into cursor badtbl5