Merging data from two table

I am answering the question asked by bjboudre here on MSDN SQL forum, on the below link.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/df74505c-99bd-4457-9e91-332435ef7183/need-help-with-query-to-join-data-from-2-tables


Scenario:

I have 2 tables that I am trying to create 1 cumulative temp table from. Table 1 has the columns COMPUTERNAME, ATTRIB1, ATTRB2, ATTRIB3. Table 2 has the columns COMPUTERNAME, ATTRIB4.  My goal is to create 1 table that shows a list of computer names and a value for each attribute.  Each COMPUTERNAME entry would be unique within a table (the same COMPUTERNAME would not be in the same table more than once but the same COMPUTERNAME may be in table 1 and table 2).  The challenge I have is that there are records that are in table 1 but not in table 2 (they would not have any data for ATTRIB4), there are records that are in both tables (they would have data for each attribute 1 thru 4), and there are records that are not in table 1 but are in table 2 (they would not have values for attributes 1 thru 3 but would have data for attribute 4).

Here is the example of tables.

Table 1:

COMPUTERNAME ATTRIB1 ATTRIB2 ATTRIB3

Machine1                1             0           1

Machine3                1             0           1

Table 2:

COMPUTERNAME  ATTRIB4

Machine1                 15

Machine2                  7

Solution:

This is a typical scenario, which has been handled in the below script. I hope this will help.

create table #Table1 (COMPUTERNAME VARCHAR(20), ATTRIB1 INT, ATTRIB2 INT, ATTRIB3 INT)

create table #Table2 (COMPUTERNAME VARCHAR(20), ATTRIB4 INT)

INSERT INTO #Table1 VALUES ('Machine1', 1, 0, 1)

INSERT INTO #Table1 VALUES ('Machine3', 1, 0, 1)

INSERT INTO #Table2 VALUES ('Machine1', 15)

INSERT INTO #Table2 VALUES ('Machine2', 7)

SELECT T_Comp.COMPUTERNAME, T1.ATTRIB1, T1.ATTRIB2, T1.ATTRIB3,T2.ATTRIB4 FROM

      (SELECT COMPUTERNAME FROM #Table1 UNION SELECT COMPUTERNAME FROM #Table2) T_Comp

LEFT OUTER JOIN #Table1 T1 ON T_Comp.COMPUTERNAME = T1.COMPUTERNAME

LEFT OUTER JOIN #Table2 T2 ON T_Comp.COMPUTERNAME = T2.COMPUTERNAME

DROP TABLE #Table1

DROP TABLE #Table2