No need to GROUP BY...
If you're "leaving out" records, the issue comes from the WHERE clause or join condition.
FROM FORCE nDebit1 LEFT JOIN companyid ;
ON ALLTRIM(nDebit1.companyid) == ALLTRIM(companyid.companyid)
A couple questions:
1. Do you have records which you want to appear, but they would only be captured by a RIGHT JOIN (CompanyID.CompanyID values not matched by nDebit1.CompanyID)?
2. Do the missing records not meet the join condition?
This is a data-driven issue, and the answer will be found in how your data matches the formulation of the query.
Jay
>I have this code:
>
>SELECT nDebit1.*, companyid.bankname AS bank, companyid.companyid AS newid ;
> FROM FORCE nDebit1 LEFT JOIN companyid ;
> ON ALLTRIM(nDebit1.companyid) == ALLTRIM(companyid.companyid) ;
> ORDER BY nDebit1.bankname, nDebit1.settdate, nDebit1.mid, ;
> nDebit1.amount, nDebit1.orgtracnum, nDebit1.dbaname, nDebit1.reasoncode, ;
> nDebit1.orgtracnum, nDebit1.cleared ;
> INTO TABLE ndebit2
>
>works okay most of the time. Every now anthen it leaves out a few records. I read in the HAckers Guide that you should use GROUP BY when you have a calculation of some type in the Select statement. Does that apply when you are joining two tables?