Multiple left joins with common fields are overwritten

Question

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!

Answer 1

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/#&togetherjs=E7luJAKsF5