SQL Row_Number() help

When joining the below tables, I am getting additional (unwanted/duplicate) rows.
select t.TransactionId, t.PartyId, t.Amount, t.Date, t.PaymentCheckId
from [Transaction] t 
where t.Date = '4/2/2015'

select pc.PaymentCheckId, pc.CheckNumber, pc.FeeAmount
from PaymentCheck pc
where pc.PaymentCheckId = 2976

select pcra.PaymentCheckId, pcra.CustomerAccountId, pcra.Amount
from PaymentCheckRelatedAccount pcra 
where pcra.PaymentCheckId = 2976

Open in new window


The code above produces the following results:

TransactionId     PartyId     Amount     Date                 PaymentCheckId
653232                144138     103.00       2015-04-02      2976
653233                144114      104.00      2015-04-02      2976

PaymentCheckId   CheckNumber    FeeAmount
2976                           7299                    8.00

PaymentCheckId  CustomerAccountId    Amount
2976                        144138                          103.00
2976                        144114                          104.00

Desired Results:
This is an example of bulk payment. Meaning a single PaymentCheck was used to make a payment for multiple CustomerAccountId's/PartyId's. The FeeAmount related to the PaymentCheck is not a fee for each PartyId. It is a fee for the PaymentCheck. So the first FeeAmount should be the FeeAmount, but all others related to this PaymentCheckId should be 0.00.

TransactionId   PartyId      Amount     Date              PaymentCheckId     CheckNumber     FeeAmount
653232               144138     103.00        2015-04-02   2976                           7299                     8.00
653233               144114     104.00        2015-04-02   2976                           7299                     0.00

My only problem is that when I join to the PaymentCheckRelatedAccount, my results are doubled / duplicated.

The ONLY reason I need to even join to this table is because I found a solution to my above problem using ROW_NUMBER(), but now my records are duplicating. Instead of 2 rows, I get 4. I tried using SELECT DISTINCT, and that removes one of the rows, but still, there's an extra row.

Here's the query I'm trying to use:
select 
t.TransactionId, t.PartyId, t.Amount, t.Date, 
pc.PaymentCheckId, pc.CheckNumber, pc.FeeAmount
, ISNULL((CASE WHEN (ROW_NUMBER() OVER (PARTITION BY pc.CheckNumber, pc.PaymentSourceId ORDER BY pcra.PaymentCheckRelatedAccountId) > 1) AND (pc.PaymentSourceId IN (2,3,4)) THEN 0 ELSE PC.FeeAmount END),0) as ServiceFees

from [Transaction] t
LEFT JOIN PaymentCheck pc ON pc.PaymentCheckId = t.PaymentCheckId
LEFT JOIN PaymentCheckRelatedAccount pcra ON PC.PaymentCheckId = pcra.PaymentCheckId

where t.Date = '4/2/2015'

Open in new window


Results:
TransactionId  PartyId Amount  Date              PaymentCheckId  CheckNumber  FeeAmount  ServiceFees
653232             144138 103.00    2015-04-02   2976                        7299                  8.00               8.00
653233             144114 104.00    2015-04-02   2976                        7299                  8.00               0.00
653233             144114 104.00    2015-04-02   2976                        7299                  8.00               0.00
653232             144138 103.00    2015-04-02   2976                        7299                  8.00               0.00

How do I properly join without causing duplication of records?
LVL 8
pzozulkaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
quick try, use distinct or group by clause?

select distinct t.TransactionId, t.PartyId, t.Amount, t.Date, pc.PaymentCheckId, pc.CheckNumber, pc.FeeAmount , ISNULL((CASE WHEN (ROW_NUMBER() OVER (PARTITION BY pc.CheckNumber, pc.PaymentSourceId ORDER BY pcra.PaymentCheckRelatedAccountId) > 1) AND (pc.PaymentSourceId IN (2,3,4)) THEN 0 ELSE PC.FeeAmount END),0) as ServiceFees from [Transaction] t LEFT JOIN PaymentCheck pc ON pc.PaymentCheckId = t.PaymentCheckId LEFT JOIN PaymentCheckRelatedAccount pcra ON PC.PaymentCheckId = pcra.PaymentCheckId where t.Date = '4/2/2015'

or

select t.TransactionId, t.PartyId, t.Amount, t.Date, pc.PaymentCheckId, pc.CheckNumber, pc.FeeAmount , ISNULL((CASE WHEN (ROW_NUMBER() OVER (PARTITION BY pc.CheckNumber, pc.PaymentSourceId ORDER BY pcra.PaymentCheckRelatedAccountId) > 1) AND (pc.PaymentSourceId IN (2,3,4)) THEN 0 ELSE PC.FeeAmount END),0) as ServiceFees from [Transaction] t LEFT JOIN PaymentCheck pc ON pc.PaymentCheckId = t.PaymentCheckId LEFT JOIN PaymentCheckRelatedAccount pcra ON PC.PaymentCheckId = pcra.PaymentCheckId where t.Date = '4/2/2015'
group by
t.TransactionId, t.PartyId, t.Amount, t.Date, pc.PaymentCheckId, pc.CheckNumber, pc.FeeAmount , ISNULL((CASE WHEN (ROW_NUMBER() OVER (PARTITION BY pc.CheckNumber, pc.PaymentSourceId ORDER BY pcra.PaymentCheckRelatedAccountId) > 1) AND (pc.PaymentSourceId IN (2,3,4)) THEN 0 ELSE PC.FeeAmount END),0)
0
pzozulkaAuthor Commented:
I mentioned in my original post that I tried DISTINCT, but that only removed one row out of the four.
0
Ryan ChongCommented:
for records in table: PaymentCheckRelatedAccount

PaymentCheckId  CustomerAccountId    Amount      PaymentCheckRelatedAccountId
2976                        144138                          103.00                      ??
2976                        144114                          104.00                      ??

will them have same value of PaymentCheckRelatedAccountId ?
0
PortletPaulfreelancerCommented:
FROM [Transaction] t
LEFT JOIN PaymentCheck pc
      ON pc.PaymentCheckId = t.PaymentCheckId
LEFT JOIN PaymentCheckRelatedAccount pcra
      ON PC.PaymentCheckId = pcra.PaymentCheckId
      AND t.PartyId = pcra.CustomerAccountId

you need an extra condition for the matching of transaction to account I believe

With that change you can get this result:
| TransactionId | PartyId | Amount |                    Date | PaymentCheckId | CheckNumber | FeeAmount | ServiceFees |
|---------------|---------|--------|-------------------------|----------------|-------------|-----------|-------------|
|        653232 |  144138 |    103 | April, 02 2015 00:00:00 |           2976 |        7299 |         8 |           8 |
|        653233 |  144114 |    104 | April, 02 2015 00:00:00 |           2976 |        7299 |         8 |           0 |

Open in new window

see it working here: http://sqlfiddle.com/#!3/f4bf47/7

note you are missing columns/data for
pc.PaymentSourceId
and
pcra.PaymentCheckRelatedAccountId
in your sample data
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pzozulkaAuthor Commented:
That worked, thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.