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?

@Francis Fuerte, sure, I added my query to the original post. — myleslee
– myleslee, Commented Jul 25, 2013 at 2:10

Timothy Walters · Accepted Answer · 2013-07-24 02:37:29Z

2

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.

answered Jul 24, 2013 at 2:37

Timothy Walters

16.9k2 gold badges44 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

myleslee Over a year ago

looks like this is more error-proof. The only concern I have is about the performance. The prototype I gave above only involves 3 tables. In reality, there are more tables to join so the nesting will get deeper.

Gordon Linoff · Accepted Answer · 2013-07-24 02:35:55Z

0

The easiest way to do this is with union all and aggregation:

select id, max(Col_A) as Col_A, max(Col_B) as Col_B, max(Col_C) as Col_c
from ((select id, col_A, NULL as col_B, NULL as COL_C
       from @a
      ) union all
      (select id, NULL, col_B, NULL
       from @b
      ) union all
      (select id, NULL, NULL, COL_C
       from @c
      )
     ) abc
group by id;

answered Jul 24, 2013 at 2:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

jtweeder · Accepted Answer · 2013-07-24 02:38:28Z

0

Here is a great article on join types that would be beneficial to read through.

http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/

Assuming you want to keep the identity of where the data came from. Col A, Col B, Col C

Not sure what you are expecting in your return, but from what I can see you should change to an inner join between your tables. Just remember you can only join one table at a time.

answered Jul 24, 2013 at 2:38

jtweeder

7593 silver badges19 bronze badges

Comments

Elmer · Accepted Answer · 2013-07-24 02:47:53Z

0

select  case when a.ID IS not null then a.ID 
        when b.ID IS not null then b.ID 
        when c.ID IS not null then c.ID 
    end 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 1

Hope this helps

answered Jul 24, 2013 at 2:47

Elmer

2723 silver badges16 bronze badges

Comments

Nithesh Narayanan · Accepted Answer · 2013-07-24 06:45:07Z

0

Try this

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

Result

enter image description here

answered Jul 24, 2013 at 6:45

Nithesh Narayanan

11.8k35 gold badges102 silver badges140 bronze badges

7 Comments

myleslee Over a year ago

if the order of tables being joined changes, the result changes:

Nithesh Narayanan Over a year ago

how? can you please explain?

myleslee Over a year ago

Sure, I have put the code snippet and results in the context of my original question.

Nithesh Narayanan Over a year ago

the order defined by you? then why you going for rea-arranging. also a sub-query which you chosen as answer now wont give you a better performance. Actually what leads you to change the order of join?

Timothy Walters Over a year ago

The issue with your join is that if B & C have a row for an ID but A doesn't, you'll get two rows with that ID (since there's no join on B->C or a coalesce on A->B, to see the issue add insert into @b values ('d', 1) to your inserts. Note that this can be fixed by adding OR b.ID=c.ID to your 2nd join.

|

Collectives™ on Stack Overflow

Combine columns from different tables (SQL Server)

5 Answers 5

1 Comment

Comments

Comments

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related