title: JOIN different data types using case statement MS SQL Server?
body: <p>I want to join t1 to t2 but the problem is i want to join a varchar to an int.
  lets say they have a column called jobType and the vales for table 1 look like </p>
  
  <pre><code>1
  2
  3
  4
  </code></pre>
  
  <p>and table 2 jobType Column looks like</p>
  
  <pre><code>a
  b
  c
  d
  </code></pre>
  
  <p>where 1 corresponds to a, 2 corresponds to b etc.</p>
  
  <p>Basically I want to be able to join job types and define that if 'a' then 1 and
  'b' then 2 and so forth.</p>
  
  <p>Below is some incorrect syntax but maybe it gets my point across, or maybe it just confuses the situation more...</p>
  
  <pre><code>select * from table1 t1
  JOIN table2 t2
  on case 
  when jobtype = 'a' then 1
  when jobtype = 'b' then 2
  when jobtype = 'c' then 3
  when jobtype = 'd' then 4
  end as jobtype
  t1.jobtype = t2.jobtype
  </code></pre>
  
  <p>Im sure this has been solved before but i dont know what this is called so google searching was unfruitful.</p>
  
  <p>EDIT: here are tables with one of the answers provided. when i run this i get 'conversion failed when converting the varchar value 'a' to data type int'. any ideas on how to do a join between different data types?</p>
  
  <pre><code>create table t1
  (id int identity(1,1),
  jobtype int,
  primary key (id)
  )
  insert into t1 (jobtype) values (1)
  insert into t1 (jobtype) values (2)
  insert into t1 (jobtype) values (3)
  insert into t1 (jobtype) values (4)
  
  create table t2
  (id int identity(1,1),
  jobtype varchar(1),
  primary key (id)
  )
  insert into t2 (jobtype) values ('a')
  insert into t2 (jobtype) values ('b')
  insert into t2 (jobtype) values ('c')
  insert into t2 (jobtype) values ('d')
  
  select * 
  from  t1
  JOIN  t2
  on case t1.jobtype
      when 'a' then 1
      when 'b' then 2
      when 'c' then 3
      when 'd' then 4
      end = t2.jobtype
  </code></pre>