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.
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
No comments:
Post a Comment