Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
o9z
Starting Member
23 Posts |
 Posted - 2007-02-05 : 12:14:38
|
| I have been working on a data export for quite some time now and have some issues.What happens is as follows:Data is entered by the user on the main form which contains sales information. The app is basically a data repository. Nothing more. The data stored in the SQL table is then exported to our accounting system. The problem I am running into is with my JOIN clause of my select. I only have one way to tie into the other SQL table. I am joining KS.Customer(main data table) to SALESCODE.CUSTOMER(the table that stores the account code for each customer). The problem is that there are multiple entries for each customer. Example:Customer - Account Code - DescriptionCustA 0001 Market Sales - CustACustA 0002 Resales - CustACustA 0003 Weight Fees - CustACustA 0004 PPF - CustACustB 0004 Market Sales - CustBCustB 0005 Resales - CustBCustB 0006 Weight Fees - CustBCustB 0007 PPF - CustBHere is where the problem comes. My export is writing the exact data I want to the file.....except it is writing it THREE times per record since my join finds 3 matches in the SALESCODE table.Is there an easy way to get around this?The complexity of the export is much more than I just explained.The export might look something like thisInvoice Custnum invdate salecode quantity price extamt descrK00001 CustA 2-1-07 0001 140 61.40 8596.47 Market Sales - CustAK00001 CustA 2-1-07 0004 140 -0.64 -89.36 PPF - CustAK00001 CustA 2-1-07 0003 140 -0.05 -7.00 Weight Fees - CustAK00001 CustA 2-1-07 /C 0 0 0 Market Sales - This is only a comment line interpreted by the accounting systemK00001 CustA 2-1-07 0001 3.26 46.01 150.00 Market Sales - CustAK00001 CustA 2-1-07 0004 3.26 -0.38 -1.25 PPF - CustAK00001 CustA 2-1-07 /C 0 0 0 ResalesThe first 4 lines are part of the "Market" block of the export. The 2nd 3 lines are part of the "Resales" block of the export. The first line uses OVERALL number to figure the calculates for quantity, price, extamt. The 2nd lines uses overall PPF fees to calculate the deductions. The 3rd lines uses the overall weight fees.The 2nd block(resales) looking in the database for the Resales_market, Resales_PPF, Resales_WeightFee fields. If the resales fields contain "0" it will not write these lines.So the problem I am having is not getting the correct data to the export, but rather getting TOO MUCH of the correct data to the export. Because my join is finding matches, it will go through and write the correct stuff for each invoice #, but add it 3-fold.Anyone have an easy solution to this, or need more information to understand it? I know it's a PITA!!!!EDIT: Here is what my SQL statement looks likeSELECT KSInvoice, CUSTOMER, LOCATION, Finisher_Loc, SHIPDATE, totalnumber, pay, Weight_Fee, WEIGHT, RESALES_PAY, RESALES_WEIGHT, RESALES_numb, resales_ppf, salecode.Customer, salecode.SaleType, salecode.SALECODE, salecode.SALESACCT, salecode.[desc]FROM KS LEFT JOIN SALECODE ON KS.Customer = salecode.customerWHERE exported IS NULLWhen I only have 1 invoice in the KS table, and I run this query, I will return 3 records.(This is not the exact output, but a stripped down version of what it would be)K00001 CustA market lineK00001 CustA ppf lineK00001 CustA weight fee lineNOTE: I edited a lot of things out of here simply because there is MUCH more to this than I wrote out. There are a few more blocks to get written to the file and more fields to use. I tried to strip it down to give an idea as to what I am wanting to do.Is there a way to modify my select to limit what it finds to 1 match per invoice? |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-02-05 : 15:26:31
|
| If 3 rows match, and you want to return only a single row, which row do you want returned ?? Hopefully you can see that you need to make some logical decisions regarding your data before you can just get the "right" answer. Think of it this way: What if I tell you I have a table of states, and a table of cities, and I want you to return all states and all cities, but only 1 row per state. (i.e., I want only 50 rows returned). Forget about writing code to do this -- the first step is to logically define how you want to calculate these results .... i.e., do we return the first city listed alphabetically, or the one with the largest population, or the capital, or a comma-separated list of all cities stuffed into 1 column, or just the count of total cities in the state, and so on.You need to decide upon that specification before anyone can write the SQL to achieve those results.- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
|
|
|
|
|
VB.Net 2005 - SQL Server 2005 - Data Issue