tsql - SQL Where clause excluding more than it should -

i have 2 tables join , clause. example contents of 2 tables:

id         fielda                   id       fieldb  1           100                     1        yellow  2           100                     2        green  3           200                     3        green  4           200                     4        blue  5           300                     5        yellow  6           300                     6        orange 

i trying return except fielda = 200 , fieldb = green. should still return line 2 has fielda = 100 , fieldb = green. however, here query , not working. excluding rows 200 , green in them:

select t1.fielda, t2.fieldb test1 t1  join test2 t2 on t1.id = t2.id (t1.fielda <> 200 , t2.fieldb <> 'green') 

the way see it, after running query row excluded should row 3 because has fielda = 200 , fieldb = green, instead returns row1, row 5 & row6. seems me should if using or.

let me know going wrong, , here ddl can play it:

create table dbo.test1 (     id int not null,     fielda int )  create table dbo.test2 (     id int not null,     fieldb varchar(10) )  insert test1 (id, fielda) values      (1,100),     (2,100),     (3,200),     (4,200),     (5,300),     (6,300)  insert test2 (id, fieldb) values      (1,'yellow'),     (2,'green'),     (3,'green'),     (4,'blue'),     (5,'yellow'),     (6,'orange') 

each condition being evaluated independently against whole set of rows. combine them, flip operators , negate combination, so:

select t1.fielda, t2.fieldb test1 t1  join test2 t2 on t1.id = t2.id not (t1.fielda = 200 , t2.fieldb = 'green') 

your original query saying, first eliminate rows fielda not 200, , then, rows remain, eliminate ones fieldb not 'green'.

when want both conditions apply given row, first select conditions want exclude, why switch <> =, make where clause exclude whole thing applying not operator.

edit re: comment

i think confusion results returned original query , idea conditions in parenthesis "evaluated one" might stem fact logical negation not distributive, i.e. negation of a && b not ~a && ~b, rather ~(a && b).

your first sentence describing results want pretty close correct t-sql query. "i trying return except fielda = 200 , fieldb = green." last part of sentence clause, i.e.

except fielda = 200 , fieldb = green 

substitute "not" "except"

not fielda = 200 , fieldb = green -- or, make grouping explicit not (where fielda = 200 , fieldb = green) 

and clean valid t-sql syntax

where not (fielda = 200 , fieldb = green) 

by contrast, english equivalent where (t1.fielda <> 200 , t2.fieldb <> 'green') might be: return field1 200 , field1 green. in case match either 200 or green sufficient exclude row.

to see why rows 2 , 4 erroneously excluded, consider truth table original clause:

                       field1 <> 200                           t       f                      -----------------                    t |   t   |   f   |                      |       | row 4 | field2 <> 'green'    -----------------                    f |   f   |   f   |                      | row 2 |       |                      ----------------- 

in other words, row 2 gets excluded because field2 = 'green', making condition field2 <> 'green' evaluate false, doesn't matter field1 is, because false , other value false.