how can I do a FULL join with 3 tables . I want to join these 3 tables with SSN.
I know I can join the 2 tables first then use the result table to join to the third table but I have 5 tables to join ..
Table A
SSN Color
1 RED
2 Green
3 Blue
Table B
SSN Color
2 Green
4 Purple
5 Yellow
Table C
SSN Color
4 Purple
6 Black
7 Pink
Result Table :
SSN TableA TableB TableC
1 RED
2 GREEN Green
3 Blue
4 Purple Purple
5 Yellow
6 Black
7 Pink
How about something like
with cte as (
select ssn from tableA
union
select ssn from tableB
union
select ssn from tableC
union
select ssn from tableD
union
select ssn from tableE
)
select cte.ssn, tablea.color, tableb.color, tablec.color.......
from cte
left join tablea on cte.ssn = tablea.ssn
left join tableb on cte.ssn = tableb.ssn
left join .....
Thank you. I love it.
Why not just use the FULL OUTER JOIN to include as many tables as desired?select
coalesce(a.ssn, b.ssn, c.ssn) ssn, a.color, b.color, c.color
from
@C1 a
full outer join
@C2 b
on a.color = b.color
full outer join
@C3 c
on c.color = b.colorAdd more tables and columns as desired.