Have Geek Brain

How can I conditionally select a field from one of the two tables?

I have two tabular accounts and pending_payments, both of which share the following lines in common: invoice_id and balance. I want to make a selection in MySQL that will work like this:

[pseudocode]

if(invoice_id exists in pending_payments table) {
    select balance from pending_payments where invoice_id = yadayadayada
} else {
    select balance from invoices where invoice_id = yadayadayada
}

Is this possible in MySQL? If so, how?

+2


a source to share


2 answers 


select i.invoice_id, coalesce(pp.balance, i.balance) as Balance
from invoices i
left outer join pending_payments pp on i.invoice_id = pp.invoice_id


Let me know if there can be multiple lines in pending_payments

for the same invoice_id

and I will suggest an alternative solution.

+4


a source


I like OrbMan's solution (I think it's the most intuitive), but here's another way - select all from one table and then the ones you didn't select from the second.

select invoice_id, balance from pending_payments where invoice_id = yadayadayada
UNION
select invoice_id, balance from invoices where invoice_id = yadayadayada
  AND invoice_id not in (select invoice_id from pending_payments)

and as a side note, some have stated that it is faster on some systems. I don’t make such claims, but if speed is an issue, it’s worth a try.



Also, if you need to include the original column (like pending or invoice), it's easy to do this like this:

select 'pending' as source, invoice_id, balance from pending_payments where invoice_id = yadayadayada
UNION
select 'invoice' as source, invoice_id, balance from invoices where invoice_id = yadayadayada
  AND invoice_id not in (select invoice_id from pending_payments)
+1


a source






More articles:


All Articles