Full outer merge of more than two tables in ms sql server to prepare desired table as shown in the e-CodePudding

I have 3 tables as shown below.

t1:

id     runs
001    1200
020    600
123    1500

t2:

id     wickets
008    4
030    7
123    0
020    6

t3:

id     catches
007    4
030    
123    2
040    6

I would like to perform FULL OUTER JOIN of all the three tables and prepare the as shown below.

Expected output:

id     runs      wickets     catches
001    1200
020    600       6
123    1500      0           2
008              4
030              7
007                          4
040                          6

I tried below code and did not works.

SELECT *
FROM t1
FULL OUTER JOIN t2
ON t1.id = t2.id
FULL OUTER JOIN t2.id = t3.id

I did the same using pandas using following code and it worked well.

from functools import reduce
dfl=[t1, t2, t3]
df_merged = reduce(lambda  left,right: pd.merge(left,right,on=['id'],
                                            how='outer'), dfl)

CodePudding user response：

You can select the expected columns you want to obtain from each table:

SELECT coalesce(t1.id,t2.id, t3.id), t1.runs, t2.wickets, t3.catches
FROM  t1
FULL OUTER JOIN  t2 ON t1.id = t2.id
FULL OUTER JOIN  t3 ON COALESCE(t1.id, t2.id) = t3.id

CodePudding user response：

You could use UNION ALL in a sub-query and then GROUP BY.
This would give you zeros where there is no value. If this is a problem we could modify the presentation.
If you have another table with all the players we could us LEFT JOIN onto the three tables with
WHERE runs <>'' OR wickets <> '' OR catches <> ''

select
sum(runs) "runs",
sum(wickets) "wickets",
sum(catches) "catches) 
from(
select id, runs, 0 wickets, 0 catches from t1
  union all
select id, 0, wickets,0 from t2
  union all
select id, 0,0, catches from t3
)
group by id,
order by id;

CodePudding user response：

This is one way to achieve the result set you require

declare @t1 table(id varchar(10),runs int)
declare @t2 table(id varchar(10),wickets int)
declare @t3 table(id varchar(10),catches int)

insert into @t1
values('001',1200),('020',600),('123',1500)

insert into @t2
values('008',4),('030',7),('123',0),('020',6)

insert into @t3
values('007',4),('030',null),('123',2),('040',6)

select coalesce(t1.id,t2.id,t3.id)id,t1.runs,t2.wickets,t3.catches
from
@t1 t1
full outer join 
@t2 t2
on t1.id = t2.id
full outer join @t3 t3
on
t1.id = t3.id
where t1.runs is not null
or t2.wickets is not null
or t3.catches is not null