Tables
transactions t
transaction_type | transaction_name |
---|---|
sale | one |
sale | two |
sales s
name | amount |
---|---|
one | 10.0 |
two | 5.0 |
purchases p
name | amount |
---|
Wanted
transaction_type | transaction_name | amount |
---|---|---|
sale | one | 10.0 |
sale | two | 5.0 |
Tried
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
Got
transaction_type | transaction_name | amount |
---|---|---|
sale | one | null |
sale | two | null |
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.
TIA!