Reputation: 83
How do I join these two tables
select m.caseno,
sum(m.BalanceAmount) GOVT from TABLE1 m
inner join TABLE2 g on m.Code = g.Code
where g.alpha in ('a','b','c')
group by m.caseno
OUTPUT CASE | GOVT
select m.caseno,
sum(m.BalanceAmount) MIF from TABLE1 m
inner join TABLE2 g on m.Code = g.Code
where g.alpha in ('d','e')
group by m.caseno
OUTPUT CASE | MIF
I want them to join in a single query CASE | GOVT | MIF
Upvotes: 2
Views: 130
Reputation: 534
you have to perform a full outer join i think. try this.
SELECT *
FROM (
select m.caseno,
sum(m.BalanceAmount) GOVT from TABLE1 m
inner join TABLE2 g on m.Code = g.Code
where g.alpha in ('a','b','c')
group by m.caseno
) AS tab1
FULL OUTER JOIN (
select m.caseno,
sum(m.BalanceAmount) MIF from TABLE1 m
inner join TABLE2 g on m.Code = g.Code
where g.alpha in ('d','e')
group by m.caseno
) AS tab2
ON tab2.caseno = tab1.caseno
Upvotes: -1
Reputation: 176
use case when to show as 2 columns.
select m.caseno,
sum(case when g.alpha in ('d','e') then m.BalanceAmount else 0 end) MIF ,
sum(case when g.alpha in ('a','b','c') then m.BalanceAmount else 0 end) GOVT
from TABLE1 m
inner join TABLE2 g on m.Code = g.Code
where g.alpha in ('a','b','c','d','e')
group by m.caseno
Upvotes: 1
Reputation: 48865
If you don't want to touch the existing queries, you can do a FULL OUTER JOIN
with them:
select
coalesce(a.caseno, b.caseno) as caseno,
a.govt,
b.mif
from ( -- first query
select m.caseno,
sum(m.BalanceAmount) GOVT from TABLE1 m
inner join TABLE2 g on m.Code = g.Code
where g.alpha in ('a','b','c')
group by m.caseno
) a
full outer join ( -- second query
select m.caseno,
sum(m.BalanceAmount) MIF from TABLE1 m
inner join TABLE2 g on m.Code = g.Code
where g.alpha in ('d','e')
group by m.caseno
) b on a.caseno = b.caseno
The FULL OUTER JOIN
is necessary -- as well as the COALESCE()
-- to join non-matching rows that may show up in either queries.
Upvotes: 3