Multiple left joins with common fields are overwritten 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!
You can use Coalesce to show s.amount or p.amount which one is not null:
Query:
select t.transaction_type, t.transaction_name, coalesce( s.amount, p.amount) 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

create schema:
create table transactions(transaction_type  varchar(30),transaction_name varchar(30));
insert into transactions values('sale','one');
insert into transactions values('sale','two');
insert into transactions values('purchase','two');

                                                                                 
create table sales (name    varchar(30), amount float);
insert into sales values('one', 10.0);
insert into sales values('two', 5.0);

create table purchases (name    varchar(30),amount float);
insert into purchases values('two', 7.0);
                                                 

Check the db-fiddle link:
https://www.db-fiddle.com/#&amp;togetherjs=E7luJAKsF5