Combine columns from different tables (SQL Server) -

i want combine 3 tables one. i'm using sql server 2005. tried full outer join got duplicate ids in results. appreciated.

+---------------+   +---------------+    +---------------+  | id   col_a    |   | id    col_b   |    | id    col_c   | +---------------+   +---------------+    +---------------+ |    2        |   | b     1       |    |     1       | | c    1        |   | c     1       |    | d     1       | +---------------+   +---------------+    +---------------+ 

results:

+---------------------------+ | id   col_a  col_b  col_c  | +---------------------------+    |    2      null   1      |  | b    null   1      null   | | c    1      1      null   | | d    null   null   1      | +---------------------------+ 

each table has different rows of data.

here's code creates 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 @a values ('a', 2) insert @a values ('c', 1)  insert @b values ('b', 1) insert @b values ('c', 1)  insert @c values ('a', 1) insert @c values ('d', 1)  select * @a select * @b select * @c 

thank you!

---

@francis fuerte, full-join query looks this:

select  coalesce(c.id,a.id,b.id) id        ,a.col_a        ,b.col_b        ,c.col_c  @c c  full outer join @a on c.id=a.id full outer join @b b on c.id=b.id   order id 

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

select  isnull(isnull(a.id,b.id),c.id) id        ,a.col_a        ,b.col_b        ,c.col_c  @a  full outer join @b b on a.id=b.id   full outer join @c c on a.id=c.id order id  select  isnull(isnull(b.id,a.id),c.id) id        ,a.col_a        ,b.col_b        ,c.col_c  @b b  full outer join @a on b.id=a.id   full outer join @c c on b.id=c.id order id  select  isnull(isnull(c.id,b.id),a.id) id        ,a.col_a        ,b.col_b        ,c.col_c  @c c  full outer join @b b on c.id=b.id   full outer join @a on c.id=a.id order id 

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

+---------------------------+ | id   col_a  col_b  col_c  | +---------------------------+    |    2      null   1      |  | b    null   1      null   | | c    1      1      null   | | d    null   null   1      | +---------------------------+ +---------------------------+ | id   col_a  col_b  col_c  | +---------------------------+    |    2      null   null   |  |    null   null   1      | | b    null   1      null   | | c    1      1      null   | | d    null   null   1      | +---------------------------+ +---------------------------+ | id   col_a  col_b  col_c  | +---------------------------+    |    2      null   1      |  | b    null   1      null   | | c    null   1      null   | | c    1      null   null   | | d    null   null   1      | +---------------------------+ 

do need add layer groupby clause avoid duplicates?

given sample there, following work:

select isnull(ab.id, c.id) [id], ab.col_a, ab.col_b, c.col_c (     select isnull(a.id, b.id) [id], a.col_a, b.col_b     @a     full outer join @b b         on a.id = b.id ) ab full outer join @c c     on ab.id = c.id order isnull(ab.id, c.id) 

basically, full outer join of a+b, use result of join c.