Last two joins cause duplicate rows

Question

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

Answer 1

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 ;)