postgresql - Joining two tables - filtering in 2 separate conditions -

i have 2 tables: table 1

customer|seller currency|invoice #|invoice currency  abc     |usd            |123      |mxp 

i have second table store bank accounts of customer

table 2

customer | bank account | currency abc      | bank1        | mxp abc      | bank2        |  inp abc      | bank3        |  usd 

i want join these 2 tables when creating dashboard can show following:

if customer abc has bank account in currency of invoice show bank account (in case result bank1)

if customer not have bank account in currency of invoice show bank account in currency of customer (in case bank3

anything else show "no valid bank account"

when i'm joining tables bringing multiple records....how can achieve this?

let's call 2 tables customer_invoices , customer_accounts.

you can use following query two left joins (so, if no bank account present still keep left data, , 1 group by (in case has more 1 bank account in given currency, don't show 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') chosen_bank_account     customer_invoices     left join customer_accounts account_seller_currency          on     account_seller_currency.customer = customer_invoices.customer            , account_seller_currency.currency = customer_invoices.seller_currency     left join customer_accounts account_invoice_currency         on     account_invoice_currency.customer = customer_invoices.customer            , account_invoice_currency.currency = customer_invoices.invoice_currency group     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 whole setup @ dbfiddle here

---

references:

• coalesce()
• left join