Invoice LEFT JOIN SALECODE ON Invoice.Client = salecode.customer
This select statement is returning the correct data(4 records for each invoice.ksid), but that causes a problem. The SALECODE table contains data used by an accounting program, and each record in the Invoice table can match 4 times in the SALECODE table. For example, the SALECODE table might have 4 records in it with the same customer field, but just a different salecode.
Since my join finds all 4 matches, each query is returning 4 times the data that is needed. This is all used for an export file, and my actual code is doing all the data writing. The only fields I can join on are invoice.Client and Salecode.Customer. Is there a way to narrow it down to only returning 1 value for each invoice ID?