Combine columns from different tables (SQL Server)

Question

I want to combine 3 tables into one. I'm using SQL Server 2005. I tried FULL OUTER JOIN but got duplicate IDs in the results. So your help is much appreciated.

+---------------+   +---------------+    +---------------+ 
| ID   COL_A    |   | ID    COL_B   |    | ID    COL_C   |
+---------------+   +---------------+    +---------------+
| a    2        |   | b     1       |    | a     1       |
| c    1        |   | c     1       |    | d     1       |
+---------------+   +---------------+    +---------------+

Results:

+---------------------------+
| ID   COL_A  COL_B  COL_C  |
+---------------------------+   
| a    2      NULL   1      | 
| b    NULL   1      NULL   |
| c    1      1      NULL   |
| d    NULL   NULL   1      |
+---------------------------+

each table has different rows of data.

here's the code that creates the tables:

declare @a table (
    ID      char(1),
    COL_A   int
)
declare @b table (
    ID      char(1),
    COL_B   int
)
declare @c table (
    ID      char(1),
    COL_C   int
)

insert into @a values ('a', 2)
insert into @a values ('c', 1)

insert into @b values ('b', 1)
insert into @b values ('c', 1)

insert into @c values ('a', 1)
insert into @c values ('d', 1)

select * from @a
select * from @b
select * from @c

Thank you!

---

@Francis Fuerte, my full-join query looks like this:

SELECT  COALESCE(c.ID,a.ID,b.ID) AS ID
       ,a.COL_A
       ,b.COL_B
       ,c.COL_C 
FROM @c c 
FULL OUTER JOIN @a a ON c.ID=a.ID
FULL OUTER JOIN @b b ON c.ID=b.ID  
ORDER BY ID

@Nithesh, if the order of the tables being joined changes, the result changes too.

SELECT  ISNULL(ISNULL(a.ID,b.ID),c.ID) AS ID
       ,a.COL_A
       ,b.COL_B
       ,c.COL_C 
FROM @a a 
FULL OUTER JOIN @b b ON a.ID=b.ID  
FULL OUTER JOIN @c c ON a.ID=c.ID
ORDER BY ID

SELECT  ISNULL(ISNULL(b.ID,a.ID),c.ID) AS ID
       ,a.COL_A
       ,b.COL_B
       ,c.COL_C 
FROM @b b 
FULL OUTER JOIN @a a ON b.ID=a.ID  
FULL OUTER JOIN @c c ON b.ID=c.ID
ORDER BY ID

SELECT  ISNULL(ISNULL(c.ID,b.ID),a.ID) AS ID
       ,a.COL_A
       ,b.COL_B
       ,c.COL_C 
FROM @c c 
FULL OUTER JOIN @b b ON c.ID=b.ID  
FULL OUTER JOIN @a a ON c.ID=a.ID
ORDER BY ID

results: (sorry, don't have enough reputation to post images)

+---------------------------+
| ID   COL_A  COL_B  COL_C  |
+---------------------------+   
| a    2      NULL   1      | 
| b    NULL   1      NULL   |
| c    1      1      NULL   |
| d    NULL   NULL   1      |
+---------------------------+
+---------------------------+
| ID   COL_A  COL_B  COL_C  |
+---------------------------+   
| a    2      NULL   NULL   | 
| a    NULL   NULL   1      |
| b    NULL   1      NULL   |
| c    1      1      NULL   |
| d    NULL   NULL   1      |
+---------------------------+
+---------------------------+
| ID   COL_A  COL_B  COL_C  |
+---------------------------+   
| a    2      NULL   1      | 
| b    NULL   1      NULL   |
| c    NULL   1      NULL   |
| c    1      NULL   NULL   |
| d    NULL   NULL   1      |
+---------------------------+

Do I really need to add an extra layer with GROUPBY clause to avoid duplicates?

Answer 1

Given your sample there, the following will work:

SELECT ISNULL(AB.ID, C.ID) AS [ID], AB.COL_A, AB.COL_B, C.COL_C
FROM (
    SELECT ISNULL(A.ID, B.ID) AS [ID], A.COL_A, B.COL_B
    FROM @a A
    FULL OUTER JOIN @b B
        ON A.ID = B.ID
) AB
FULL OUTER JOIN @c C
    ON AB.ID = C.ID
ORDER BY ISNULL(AB.ID, C.ID)

Basically, do a FULL OUTER JOIN of A+B, then use the result of that to join to C.