Last two joins cause duplicate rows
join, sql, sql-server
Solution
The cause is that at least one of the connections `ACCOUNTBILLFEE-FOLDER` or `VALIDACCOUNTFEE-ACCOUNTBILLFEE` is not one-to-one. It allows for one Folder to have many AccountBillFees or for one ValidAccountFee to have many AccountBillFees.
To find the cause of such a problem this is what I usually do:
- Change the `SELECT A, B, C` part of your query to `SELECT *`.
- Reduce the results to one of the rows that is causing you trouble (by adding a `WHERE ...`). That is a single row without your last two joins and a few rows after you add those two joins.
- Look at the result table from left to right. The first columns will probably show the same values for all rows. Once you see a difference between the values in a column, you know that the table of the column you are currently looking at is causing your "multiple row problem".
- Now create a `SELECT *` statement that includes only the two tables joined together that cause multiple rows with the same `WHERE ...` you used above.
- The result should give you a clear picture of the cause.
- Once you know the reason for your problem you can think of a solution ;)
Problem
Ok, so I have a query that is returning more rows than expected with repeating data. Here is my query: ``` SELECT AP.RECEIPTNUMBER ,AP.FOLDERRSN ,ABS(AP.PAYMENTAMOUNT) ,ABS(AP.PAYMENTAMOUNT - AP.AMOUNTAPPLIED) ,TO_CHAR(AP.PAYMENTDATE,'MM/DD/YYYY') ,F.REFERENCEFILE ,F.FOLDERTYPE ,VS.SUBDESC ,P.NAMEFIRST||' '||P.NAMELAST ,P.ORGANIZATIONNAME ,VAF.FEEDESC ,VAF.GLACCOUNTNUMBER FROM ACCOUNTPAYMENT AP INNER JOIN FOLDER F ON AP.FOLDERRSN = F.FOLDERRSN INNER JOIN VALIDSUB VS ON F.SUBCODE = VS.SUBCODE INNER JOIN FOLDERPEOPLE FP ON FP.FOLDERRSN = F.FOLDERRSN INNER JOIN PEOPLE P ON FP.PEOPLERSN = P.PEOPLERSN INNER JOIN ACCOUNTBILLFEE ABF ON F.FOLDERRSN = ABF.FOLDERRSN INNER JOIN VALIDACCOUNTFEE VAF ON ABF.FEECODE = VAF.FEECODE WHERE AP.NSFFLAG = 'Y' AND F.FOLDERTYPE IN ('405B','405O') ``` Everything works fine until I add the bottom two Inner Joins. I'm basically trying to get all payments that had NSF. When I run the simple query: ``` SELECT * FROM ACCOUNTPAYMENT WHERE NSFFLAG = 'Y' ``` I get only 3 rows pertaining to 405B and 405O folders. So I'm only expecting 3 rows to be returned in the above query but I get 9 with information repeating in some columns. I need the exact feedesc and gl account number based on the fee code that can be found in both the Valid Account Fee and Account Bill Fee tables. I can't post a picture of my output. Note: when I run the query without the two bottom joins I get the expected output. Can someone help me make my query more efficient? Thanks! As requested, below are the results that my query is returning for vaf.feedesc and vaf.glaccountnumber columns: ``` Boiler Operator License Fee 2423809 Boiler Certificate of Operation without Manway - Revolving 2423813 Installers (Boiler License)/API Exam 2423807 Boiler Public Inspection/Certification (State or Insurance) 2423816 Boiler Certificate of Operation with Manway 2423801 Boiler Certificate of Operation without Manway 2423801 Boiler Certificate of Operation with Manway - Revolving 2423813 BPV Owner/User Program Fee 2423801 Installers (Boiler License)/API Exam Renewal 2423807 ```