CODEWITHSUNDEEP
postgresqljoinfilter
Katy
Katy

Reputation: 1

Joining two tables - filtering in 2 separate conditions

I have two tables: Table 1

Customer|Seller Currency|Invoice #|Invoice Currency

ABC     |USD            |123      |MXP

I have a second table where I store the bank accounts of my Customer

table 2

Customer | Bank Account | Currency
ABC      | BANK1        | MXP
ABC      | BANK2        |  INP
ABC      | BANK3        |  USD

I want to join these two tables so that when I am creating a dashboard I can show the following:

If the Customer ABC has a bank account in the currency of the invoice then show that bank account (in this case the result would be bANK1)

If the Customer Does NOT have a bank account in the currency of the invoice then show the bank account in the currency of the Customer (in this case it would be Bank3

Anything else does show "no valid bank account"

When I'm joining my tables it is bringing multiple records....how can I achieve this?

Upvotes: 0

Views: 36

Answers (1)

joanolo
joanolo

Reputation: 6328

Let's call your two tables customer_invoices and customer_accounts.

You can use the following query with two LEFT JOINs (so, if no bank account is present we still keep the left data, and one GROUP BY (in case somebody has more than one bank account in a given currency, we don't show all of them):

SELECT
    customer_invoices.customer, 
    customer_invoices.seller_currency, 
    customer_invoices.invoice_number, 
    customer_invoices.invoice_currency,
    coalesce( min(account_invoice_currency.bank_account), 
              min(account_seller_currency.bank_account), 
              'no valid bank account') AS chosen_bank_account
FROM
    customer_invoices
    LEFT JOIN customer_accounts AS account_seller_currency 
        ON     account_seller_currency.customer = customer_invoices.customer
           AND account_seller_currency.currency = customer_invoices.seller_currency
    LEFT JOIN customer_accounts AS account_invoice_currency
        ON     account_invoice_currency.customer = customer_invoices.customer
           AND account_invoice_currency.currency = customer_invoices.invoice_currency
GROUP BY
    customer_invoices.customer, customer_invoices.seller_currency,
    customer_invoices.invoice_number, customer_invoices.invoice_currency ;

You'll get:

customer | seller_currency | invoice_number | invoice_currency | chosen_bank_account
:------- | :-------------- | -------------: | :--------------- | :------------------
ABC      | USD             |            123 | MXP              | BANK1

You can check the whole setup at dbfiddle here

References:

Upvotes: 1

Related Questions