sql server - CASE expression for two unique values being compared to another table -

using sql-server 2008 / t-sql

background:

my query seeks compare codes assigned across 2 versions (highest value vs next highest). versions assigned same id (i.e. versions of id). however, final piece in jigsaw (where struggling) see if code assigned version of same id other previous version. way dataset built (through use of temp tables) previous version never have same code current version.

table 1:

id | curver | curcode | prevver | prevcode | ------------------------  1  |   4   | 1234    |   3     |   4321 

basically, although 1234 assigned version 4 not 3, want run yes/no or 1/0 checker on table, table 2, see if assigned versions 1 or 2 of same id.

table 2:

     id | ver   | code      ------------------------      1  |   1   | 1234       1  |   2   | 4321       1  |   3   | 4321      1  |   4   | 1234  

so, want join table 2 table 1 , introduce column table 1 gives '1' if code assigned versions not curver in table 2.

my query goes this:

select  distinct t1.id ,t1.curver ,t1.curcode ,t1.prevver ,t1.prevcode     ,(case when     t1.id = t2.id , t1.curver != t2.ver , t1.curcode = t2.code 1     else 0     end) #table1 t1  left join #table2 t2 on t1.id = t2.id 

without distinct list results 4 times 4 versions in table2, '1's against matching codes. distinct gives me dataset of:

 id | curver | curcode | prevver | prevcode | checker -----------------------------------------------------  1  |   4   | 1234    |   3     |   4321    |   1  1  |   4   | 1234    |   3     |   4321    |   0 

i don't want '0' entries appear, '1'. if there no other match in t2 shows 0. works other ids (there's 1 line) there's no '1' handle.

i grateful ideas on how not show '0' values , show '1's when code found against versions before prevver. please note doing on dataset of 4000 ids.

you can output below query ignoring 0 , showing 1 value :

select distinct                           t1.id, t1.curver, t1.curcode, t1.prevver, t1.prevcode, (case when t1.id = t2.id , t1.curver != t2.ver , t1.curcode = t2.code 1 else 0 end)                           expr1            table1 t1 left outer join                          table2 t2 on t1.id = t2.id        ((case when t1.id = t2.id , t1.curver != t2.ver , t1.curcode = t2.code 1 else 0 end) > 0)