select t.transaction_type, t.transaction_name, s.amount, p.amount from transactions t left join sales s on t.transaction_type='sale' and t.transaction_name=s.name left join purchases p on t.transaction_type='purchase' and t.transaction_name=p.name
I understand that the outer join of the purchases is overwriting the amounts from the sales because of the common field, but what is the best approach in this case?
Note that there will be more tables to join like this. This is mysql in case it makes a difference, the query has been paraphrased and there is an orm wrapper.
I know I can do it with separate queries, inner joins and unions, is that best? I was hoping to do it in one query.