title: Combine columns from different tables (SQL Server)
body: <p>I want to combine 3 tables into one. I'm using SQL Server 2005. I tried <code>FULL OUTER JOIN</code> but got duplicate IDs in the results. So your help is much appreciated.</p>
  
  <pre><code>+---------------+   +---------------+    +---------------+ 
  | ID   COL_A    |   | ID    COL_B   |    | ID    COL_C   |
  +---------------+   +---------------+    +---------------+
  | a    2        |   | b     1       |    | a     1       |
  | c    1        |   | c     1       |    | d     1       |
  +---------------+   +---------------+    +---------------+
  </code></pre>
  
  <p>Results:</p>
  
  <pre><code>+---------------------------+
  | ID   COL_A  COL_B  COL_C  |
  +---------------------------+   
  | a    2      NULL   1      | 
  | b    NULL   1      NULL   |
  | c    1      1      NULL   |
  | d    NULL   NULL   1      |
  +---------------------------+
  </code></pre>
  
  <p>each table has different rows of data. </p>
  
  <p>here's the code that creates the tables:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Thank you!</p>
  
  <hr>
  
  <p><strong>@Francis Fuerte</strong>, my full-join query looks like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>@Nithesh</strong>, if the order of the tables being joined changes, the result changes too.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>results: (sorry, don't have enough reputation to post images)</p>
  
  <pre><code>+---------------------------+
  | 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      |
  +---------------------------+
  </code></pre>
  
  <p>Do I really need to add an extra layer with GROUPBY clause to avoid duplicates?</p>