0

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!

2
  • which rdms are you using?
    – nbk
    Feb 22, 2021 at 13:28
  • mysql (says above)
    – racitup
    Feb 22, 2021 at 13:31

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.