Home > other >  Avoid duplicate values using join with multiple tables in Firebird
Avoid duplicate values using join with multiple tables in Firebird

Time:12-29

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
  • Related