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

user_8901845 0
Viewed 48

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)
tsql sql-server sql
2 Answers

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
user_96413550

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;
Admin1
Related