I have 3 tables
Payments
PaymntID | Date | Amount |
---|---|---|
1 | Ago.2021 | 500 |
2 | Sep.2021 | 1200 |
3 | Oct.2021 | 600 |
CashPayments
PaymntID | Amount |
---|---|
1 | 500 |
2 | 400 |
3 | 200 |
CreditCardPayments
PaymntID | Amount | CreditCard |
---|---|---|
2 | 450 | Visa |
2 | 350 | MC |
3 | 400 | Visa |
I need to get the PaymentID, date, amount payed in cash and amount payed in creditcard, but with the following query I'm getting duplicates.
Just a quick note, I can have two different credit cards used in one payment, but I can also have 2 different "cash payments" used in one payment.
Query:
select
PA.DATE AS PaymntDate,
PA.PaymntID AS PaymntID,
CP.amount AS CashAmount,
CCP.amount AS CreditAmount,
from Payments PA
LEFT join CashPayments CP on PA.PaymntID = CP.PaymntID
LEFT join CreditCardPayments CCP on PA.PaymntID = CCP.PaymntID
I'm getting the following result:
Result
PaymntID | PaymntDate | CashAmount | CreditAmount |
---|---|---|---|
1 | Ago.2021 | 500 | null |
2 | Sep.2021 | 400 | 450 |
2 | Sep.2021 | 400 | 350 |
3 | Oct.2021 | 200 | 400 |
So in this case, the cashpayment in paymntid 2 is duplicated. What I need is the following table:
Result
PaymntID | PaymntDate | CashAmount | CreditAmount |
---|---|---|---|
1 | Ago.2021 | 500 | null |
2 | Sep.2021 | 400 | 800 |
3 | Oct.2021 | 200 | 400 |
CodePudding user response:
You need to aggregate the values for each ID before joining. Probably the most succinct way is to use a CTE and then outer join each to your payments table, something like the following:
with cp as (
select paymentId, Sum(Amount) Amount
from CashPayments
group by PaymentId
), ccp as (
select paymentId, Sum(Amount) QAmount
from CreditCardPayments
group by PaymentId
)
select p.PaymentId, p.Date,
Coalesce(cp.Amount,0) CashAmount,
Coalesce(ccp.Amount,0) CreditAmount
from Payments p
left join cp on cp.PaymentId = p.PaymentId
left join ccp on ccp.PaymentId = p.PaymentId