Geek asks and answers

The last two connections lead to duplicate rows

So, I have a query that returns more rows than expected, with data repeating. Here is my request:

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 two bottom inner joins. I am basically trying to get all payments that were NSF. When I run a simple query:

SELECT *
FROM ACCOUNTPAYMENT
WHERE NSFFLAG = 'Y'

I get only 3 lines related to folders 405B and 405O. Therefore, I expect 3 rows to be returned in the query above, but I get 9 with information repeating in some columns. I need the exact account number feedesc and gl based on the remuneration code, which can be found both in the tables of actual commission fees and in the accounts of the account.

I cannot post an image of my output.

: , .

- ? !

, vaf.feedesc vaf.glaccountnumber:

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
+3
3

, ACCOUNTBILLFEE-FOLDER VALIDACCOUNTFEE-ACCOUNTBILLFEE . ValidAccountFee .

, , :

  • SELECT A, B, C SELECT *.
  • , ( WHERE ...). .
  • . , , . , , , , .
  • SELECT *, , WHERE ..., .
  • .
  • , ;)
+10

, , , . , , , ,

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 
(
   SELECT DISTINCT ABF.FEECODE, ABF.FOLDERRSN
   FROM ACCOUNTBILLFEE ABF
) ABF ON F.FOLDERRSN = ABF.FOLDERRSN
INNER JOIN 
(
  SELECT DISTINCT VAF.FEEDESC, VAF.GLACCOUNTNUMBER, VAF.FEECODE
  FROM VALIDACCOUNTFEE VAF
) VAF ON ABF.FEECODE = VAF.FEECODE
WHERE AP.NSFFLAG = 'Y'
AND F.FOLDERTYPE IN ('405B','405O')
0

. , , 9 , , , -, - , .

, , SQl 9 , , , , . , , , , .

, , - , . , , , , , -, , . , , , rqeuirements. , , , , where, , . -, SQL.

create table #temp (myid int , mydescription varchar(30))

insert into #temp(myid, mydescription)
values (1, 'test') , (2, 'test2')

create table #temp2 (myid int, myotherdescription varchar(30))

insert into #temp2(myid, myotherdescription)
values (1, 'othertest') , (1, 'othertest2'), (2, 'myothertest') , (1, 'othertest3')

select * 
from #temp t
join #temp2 t2 on t.myid = t2.myid

select t2.myid, t.mydescription
from #temp t
join #temp2 t2 on t.myid = t2.myid

select distinct t2.myid, t.mydescription
from #temp t
join #temp2 t2 on t.myid = t2.myid

select t.myid, t.mydescription, t2.myotherdescription
from #temp t
join #temp2 t2 on t.myid = t2.myid

select distinct t.myid, t.mydescription, t2.myotherdescription
from #temp t
join #temp2 t2 on t.myid = t2.myid

select t.myid, min(t2.myotherdescription)
from #temp t
join #temp2 t2 on t.myid = t2.myid
group by t.myid

select t.myid, t2.myotherdescription
from #temp t
join #temp2 t2 on t.myid = t2.myid
where  t2.myid = 2
0

More articles:


All Articles