-3

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?

Improve this question
2
  • 1
    What was wrong with the answers provided the first time you asked this question? stackoverflow.com/questions/7522821/… Commented Sep 23, 2011 at 2:49
  • Note that in the other version of this question, the temp_id and node_name are used as join columns. Commented Sep 23, 2011 at 6:04

5 Answers 5

Reset to default
2

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0 
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)
Improve this answer

2 Comments

I tried this but i am sure why it is not working it fetches all the records from table2.
You should use the JOIN notation.
0 
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
Improve this answer

3 Comments

Why an LOJ? The question needs a plain inner join.
@Jonathan All records from Table1 so Left outer join would be perfect.
@Jonathan. If there is records not on table 2 it will just display null but all rows from table 1 still displays.if you use inner join it will only display the exact records from both tables.
0

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/

Improve this answer

Comments

0

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)
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.