testy this one is i tell ya, testy... ok. i have a table, with 4 columns:
1. acctNo 2. trxAmt 3. trxType 4. trxId where trxType might be [d] or [r] and for every [r] there is a matching [d]. i want to get acctNo's that match but only when the trxAmt are opposing value matches... could be... acctNo: 100 trxAmt: -545.00 trxType: r trxId: 500 and the matching one would look like acctNo: 100 trxAmt: 545.00 trxType: r trxId: 510 basically a reversal of a mistaken transaction.... now, the problem lies in the fact that there may be another transaction that matches the acctNo but has nothing to do with this payment reversal. can this be done? here is what i have, and its giving me ones that match the acctNo, but are extra, since they are not matching in the dollar value... select acctNo, trxAmt from transactions where (trxtype = 'd' or trxType = 'r') and acctNo in (select acctNo from colthist where trxAmt like '%-%') group by trxAmt, acctNo here is a sample set, the first one that pulls up, and clearly illustrates the problemo ;) 127 -154.50 #1 127 154.50 #2 127 618.00 #3 133 -245.00 #4 133 -196.00 #5 133 -98.00 #6 133 25.00 #7 133 50.00 #8 133 85.11 #9 133 98.00 #10 133 196.00 #11 133 245.00 #12 133 500.00 #13 where 1 and 2 are correct but three isnt, and 4,5,6 match 10,11 and 12. but, i dont want 3,7,8,9 or 13 to be there. confused, i hope not, but i hope this is possible in a query... ....tony tony weeg [EMAIL PROTECTED] www.revolutionwebdesign.com rEvOlUtIoN wEb DeSiGn 410.334.6331 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
