SQLTeam.com | Weblogs | Forums

Full join multiple parent tables


#1

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


#2

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 .....


#3

Thank you. I love it.


#4

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.