Unexpected cartesian product in Foxpro SQL statement -

i executing following visual foxpro sql statement c# program. expecting 1 result not getting results.

select      iplclaim.lc_lname, ipcname.cn_lname, ipcbusn.cb_bname   ;      iplclaim  ; inner join      iplclsub on iplclsub.ls_claimno + iplclsub.ls_sclaimno = iplclaim.lc_claimno + 'a' ; left outer join      ipcname on ipcname.cn_inqno = iplclsub.ls_inqno  ; left outer join      ipcbusn on ipcbusn.cb_inqno = iplclsub.ls_inqno  ;      iplclaim.lc_claimno = '  1105'     ;     , not deleted()  

i able reproduce problem running vfp using following program...

close databases open database ipcust.dbc use c:\testvfp\ips\data\ppdata\iplclaim in 0 use c:\testvfp\ips\data\ppdata\iplclsub in 0 use c:\testvfp\ips\data\ppdata\ipcname in 0 use c:\testvfp\ips\data\ppdata\ipcbusn in 0 sys(3054,12)  select iplclaim.lc_lname, ipcname.cn_lname, ipcbusn.cb_bname   ;  iplclaim  ; inner join iplclsub on iplclsub.ls_claimno + iplclsub.ls_sclaimno = '  1105a'     ;  left outer join ipcname on ipcname.cn_inqno = iplclsub.ls_inqno  ;  left outer join ipcbusn on ipcbusn.cb_inqno = iplclsub.ls_inqno  ; iplclaim.lc_claimno = '  1105' ;   , not deleted()  

it seems if tables not open before program executed results 0 records. if open tables , execute sql statement returns expected 1 record.

the other thing not expect when run program above, sys(3054,12) returns following results (notice "cartesian product")...

select ipcname.cn_lname, ipcbusn.cb_bname iplclsub left outer join ipcname on ipcname.cn_inqno = iplclsub.ls_inqno left outer join ipcbusn on ipcbusn.cb_inqno = iplclsub.ls_inqno  iplclsub.ls_claimno + iplclsub.ls_sclaimno = '  1105a' , not ip using index tag claimall rushmore optimize table iplclsub rushmore optimization level table iplclsub: partial rushmore optimization level table ipcname: none rushmore optimization level table ipcbusn: none joining table iplclsub , table ipcname (cartesian product) joining intermediate result , table ipcbusn (cartesian product) 

but when run sql statement on own tables open output looks different (notice no "cartisian product")

select ipcname.cn_lname, ipcbusn.cb_bname iplclsub left outer join ipcname on ipcname.cn_inqno = iplclsub.ls_inqno left outer join ipcbusn on ipcbusn.cb_inqno = iplclsub.ls_inqno  iplclsub.ls_claimno + iplclsub.ls_sclaimno = '  1105a' , not ip using index tag claimall rushmore optimize table iplclsub rushmore optimization level table iplclsub: partial rushmore optimization level table ipcname: none rushmore optimization level table ipcbusn: none joining table iplclsub , table ipcname using tag inqno joining intermediate result , table ipcbusn using tag inqno 

final note: there 1 record in iplclaim matches where clause. there 1 record in iplclsub matches inner join iplclsub on clause. there 1 record in ipcname matches left outer join ipcname on clause , there no record in ipcbusn matches left outer join ipbusn on clause.

can explain causing problem , have resolve it?

edit:

well figured out problem and not deleted(). if remove clause works expected. can explain , can tell me alternative way exclude deleted records?

there warning in foxpro documentation select-sql command possibly returning unexpected results when using functions deleted() in multiple table queries.

the set deleted on command can used ignore records marked deletion. option turned on looks use of deleted() function removed query.

for c# code, assuming you're using oledb connection, set deleted on command can executed on same open foxpro connection prior executing query.