Problem with SQL Query in vb.net

[o9z]

SELECT
KSID, Client, LOCATION, Finisher, NPB, sort_adj, SHIPDATE, numb, pay, P_Charge, Extra_Charge, Weight_Fee, WEIGHT, DEAD_PAY, DEAD_WEIGHT, DEAD, SLOWS_PAY, SLOWS_WEIGHT, NPPC, SLOWS, RESALES_PAY, RESALES_WEIGHT, RESALES, SLOW_NPPC, SLOW_NPB, slow_weight_fee, resale_nppc, resale_npb, salecode.Customer, salecode.SaleType, salecode.SALECODE, salecode.SALESACCT, salecode.[desc]

FROM
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.

So it could be like this

Customer SaleCode

ClientA W001
ClientA W002
ClientA W003
ClientA W004
ClientB W001
ClientB W002
ClientB W003
ClientB W004

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?

[NightShift58]

Use:
GROUP BY invoice ID (to ensure you only get one record) and
ORDER BY SaleCode (to ensure you get the lowest Salecode)

[o9z]

By adding the group by clause, I get the following error

Column Invoice.Client is invalid in the select list because it is not contained in either an aggregate function or a group by clause

[NightShift58]

Add COUNT(*) in the SELECT part of the statement.

[o9z]

Not sure I am following. Is this what you mean?

SELECT Count(*) AS Test, KSID, Client, LOCATION, Finisher, NPB, sort_adj, SHIPDATE, numb_head, pay, Plant_Charge, Extra_Charge, Weight_Fee, WEIGHT, DEAD_PAY, DEAD_WEIGHT, DEAD, SLOWS_PAY, SLOWS_WEIGHT, NPPC, SLOWS, RESALES_PAY, RESALES_WEIGHT, RESALES, SLOW_NPPC, SLOW_NPB, slow_weight_fee, resale_nppc, resale_npb, salecode.Customer, salecode.SaleType, salecode.SALECODE, salecode.SALESACCT, salecode.[desc]

FROM INVOICE LEFT JOIN SALECODE ON INVOICE.CLIENT = salecode.customer

Group By Invoice.KSID
Order By SaleCode.SaleCode

[NightShift58]

Yes, that's what I meant.

[o9z]

exact same error message

[NightShift58]

What DBMS are you using?

[o9z]

SQL Server 2005

[NightShift58]

Ok. The above works on MySQL. You may have to tweak it for MSSQL