sql query to create a new table [closed]

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center. Closed 11 years ago.

I have a table as follows: table 1

temp_id    node_name  variable_1 variable_2 variable_3
1          ab         a          b           y
2          sdd        a          a           a
3          u          a          s           s

and another table as follows: table 2

开发者_如何学编程

temp_id    node_name  variable_1 variable_2 variable_3
1          ab         as        sb           y
2          sdd        a          a           a
3          u          a          s           s

I want to fetch all the records from table 1 only where the combination variable_1, variable_2 and variable_3 of table 1 doesnot match with table 2. for example in table 1 first record has a,b,y (variable_1, variable_2 and variable_3) and it this does not exists in table2.

How can I do that in TSQL?

---

It is not clear whether there a requirement for an outer join. I assume that the columns are all NOT NULL qualified; the condition gets much more complex if any of the variable_X columns can hold nulls.

SELECT T1.*
  FROM Table1 AS T1
  JOIN Table2 AS T2 ON T1.Temp_ID = T2.Temp_ID AND T1.Node_Name = T2.Node_Name
 WHERE T1.Variable_1 != T2.Variable_1
    OR T1.Variable_2 != T2.Variable_2
    OR T1.Variable_3 != T2.Variable_3;

If NULLS are allowed, then you have to write:

SELECT T1.*
  FROM Table1 AS T1
  JOIN Table2 AS T2 ON T1.Temp_ID = T2.Temp_ID AND T1.Node_Name = T2.Node_Name
 WHERE (T1.Variable_1 != T2.Variable_1
        OR (T1.Variable_1 IS NULL AND T2.Variable_1 IS NOT NULL)
        OR (T2.Variable_1 IS NULL AND T1.Variable_1 IS NOT NULL)
       )
    OR (T1.Variable_2 != T2.Variable_2
        OR (T1.Variable_2 IS NULL AND T2.Variable_2 IS NOT NULL)
        OR (T2.Variable_2 IS NULL AND T1.Variable_2 IS NOT NULL)
       )
    OR (T1.Variable_3 != T2.Variable_3
        OR (T1.Variable_3 IS NULL AND T2.Variable_3 IS NOT NULL)
        OR (T2.Variable_3 IS NULL AND T1.Variable_3 IS NOT NULL)
       );

Note that this is not the same as:

SELECT T1.*
  FROM Table1 AS T1
  JOIN Table2 AS T2 ON T1.Temp_ID = T2.Temp_ID AND T1.Node_Name = T2.Node_Name
 WHERE NOT (T1.Variable_1 = T2.Variable_1
       AND  T1.Variable_2 = T2.Variable_2
       AND  T1.Variable_3 = T2.Variable_3);

---

select * from table1 t1, table2 t2 where
t1.temp_id = t2.temp_id and
( t1.var1 != t2.var1 OR t1.var2 != t2.var2 OR t1.var3 != t2.var3)

---

select * from table1 t1
left outer join table2 t2 on t1.temp_id = t2.temp_id 
where  t1.var1 <> t2.var1 OR t1.var2 <> t2.var2 OR t1.var3 <> t2.var3

---

It is unclear to me if you want to check the existence against all rows in table2

select T1.*
from table1 as T1
where not exists (select *
                  from table2 as T2
                  where T1.variable_1 = T2.variable_1 and
                        T1.variable_2 = T2.variable_2 and
                        T1.variable_3 = T2.variable_3
                 )

             

or you you only want to check against the rows where temp_id is a match.

select T1.*
from table1 as T1
  left outer join table2 as T2
    on T1.temp_id = T2.temp_id and
       T1.variable_1 = T2.variable_1 and
       T1.variable_2 = T2.variable_2 and
       T1.variable_3 = T2.variable_3
where T2.temp_id is null        

With your test data the result is the same: https://data.stackexchange.com/stackoverflow/qt/113272/

---

How about this:

select * from Table1 T1 LEFT OUTER JOIN Table2 T2
on T1.temp_id = T2.temp_id
where WHERE (T1.variable_1 <> T2.variable_1 AND
T1.variable_2 <> T2.variable_2 AND
T1.variable_3 <> T2.variable_3)