>
>
>Since you have the FROM in the other examples, I won't mention it here <g>
>
>LEFT OUTER JOIN is your answer.
>

select transtable.*, hfcashr.* ;
>  from transtable ;
>  left outer join hfcashr ;
>  on transtable.acct_no = HFCASHR.ACCT_NO

Hello Trey,

I tried the above but still got an erroneuos answer so I made some adjustments and it came out right.

First "SQL" retrieves 32 records.
select hftrns.* from hftrns where hftrns.acct_no = ;
"26733" order by hftrns.stateno, hftrns.seqno into cursor transtable

Second "SQL" retrieves 9 records.
SELECT HFCASHR.* FROM HFCASHR WHERE HFCASHR.ACCT_NO = '26733' INTO table HFCASHTEMP (for some reason I had to change from a cursor to a table result)

Third "SQL" retrieves 288 records.
select transtable.*, hfcashTEMP.* ;
from transtable ;
left outer join hfcashTEMP ;
on transtable.acct_no = HFCASHTEMP.ACCT_NO

I ran the second "SQL" just to make the cursor specific to the acct_no but it didn't seem to help.

Next I tried a different field in the join and got accurate results.

I changed:
on transtable.acct_no = HFCASHTEMP.ACCT_NO

to
on ((transtable.seqno = HFCASHTEMP.seqno) and (transtable.stateno = HFCASHTEMP.stateno))

This gave me a result cursor of 35 records, which is correct.

It appears that I needed to be more specific on how the left outer join was filtered.

Thanks for the help.

Jim Harvey
jharvey@netrax.net