sql server - SQL Joining Based on Common Calculated Values -

using sql server 2012. querying data 2 separate tables non-overlapping key values. able calculate common key using case statements takes following form:

query #1:

select     "region" = case                   when facility = 100 region = 'north'                  when facility = 200 region = 'south'                  when facility = 300 region = 'midwest'                  else null                end,     other_variable_1, other_variable_2, ...     data1 

query #2:

select     "region" = case                   when facility = 63 region = 'north'                  when facility = 67 region = 'south"                  when facility = 89 region = 'midwest'                  else null                end,     other_variable_a, other_variable_a, ...     data2 

i tried link 2 queries running join statement on 2 case statements follows:

select      a.other_variable_1, b.other_variable_b      data1 inner join      data2 b on (case                    when a.facility = 100 region = 'north'                    when a.facility = 200 region = 'south'                    when a.facility = 300 region = 'midwest'                    else null                  end) = (case                            when b.facility = 63 region = 'north'                            when b.facility = 67 region = 'south"                            when b.facility = 89 region = 'midwest'                            else null                           end) 

the query ran on hour before gave up. there better way join 2 case statements without publishing 2 separate tables , running third query?

i know don't want tables. want share experience on same case yours. method more flexible user can change mapping between facility , region. have join 2 tables (10 millions row) computed value. solve performance issue by.

1.add 1 "region" column each table
2.every night there data processing update columns base on specified value user via mds (i assume user know relation between facility , region)

create table data1_mapping_facility_region (     facility int,     region nvarchar(60) ) create index ix_data1_mapping_facility_region_facility on data1 (facility) include (region)  create table data2_mapping_facility_region (     facility int,     region nvarchar(60) ) create index ix_data2_mapping_facility_region_facility on data2 (facility) include (region)  insert data1_mapping_facility_region values(100, 'north'), (200, 'south'), (300, 'midwest') insert data2_mapping_facility_region values(63, 'north'), (67, 'south'), (89, 'midwest') 

mapping_data1_facility_region

100  'north' 200  'south' 300  'midwest' 

mapping_data2_facility_region

63   'north' 67   'south' 89   'midwest' 

create index data1 , data2

create index ix_data1_region on data1 (region) include (other_variable_1) create index ix_data2_region on data2 (region) include (other_variable_b) 

update data1 , data2

update  data set     data.region = map.region    data1 data             inner join data1_mapping_facility_region map             on data.facility = map.facility  update  data set     data.region = map.region    data2 data             inner join data2_mapping_facility_region map             on data.facility = map.facility 

change query to

select a.other_variable_1, b.other_variable_b   data1        inner join data2 b on a.region = b.region 

compare query plan