title: Case statement that has to check through another table
body: <p>I have the table t1:</p>
  
  <pre><code>pers_key  code1  code2
         1     AA     BB
         2     AA     CC
         3     AA     DD
         4     BB     CC
         5     BB     DD
         6     CC     DD     
  </code></pre>
  
  <p>And another table t2:</p>
  
  <pre><code>code  ind_A  ind_B  ind_C
    AA      1      0      0
    BB      0      1      0
    CC      0      0      1
    DD      1      0      1
  </code></pre>
  
  <p>I want to join these tables together so that for each record in t1, we add columns ind_A, ind_B, ind_C and set them to 1 if the respective code specifies it. For example, this would be my ideal output table:</p>
  
  <pre><code>pers_key  code1  code2  ind_A  ind_B  ind_C
         1     AA     BB      1      1      0
         2     AA     CC      1      0      1
         3     AA     DD      1      0      1
         4     BB     CC      0      1      1 
         5     BB     DD      1      1      1
         6     CC     DD      1      0      1
  </code></pre>
  
  <p>So for each t1 record, we are setting the inds to 1 if its code1 OR code2 has the inds as 1 in the other table t2. What would be the best way to achieve this result? Keep in mind this is just a sample to get the idea/method, in my actual data there are hundreds of different code values. I am using HIVE/hiveQl version 0.12.</p>