title: Same Join on multiple fields in Hive
body: <p>I have a metadata table <strong>CUST</strong> as below in hive</p>
  <pre><code>Cust_dec       Cust_det
  buy            Interested
  buy            Cheap
  no_buy         Found Cheaper
  no_buy         No Interest
  no_buy         Other Faults
  </code></pre>
  <p>There's another table <strong>Reg_cst_dtls</strong> that needs to be joined with above metadata table multiple times and derive multiple fields as below.</p>
  <pre><code>item_id     ca_brd_dec     ne_brd_dec     co_brd_dec     ca_dtl             ne_dtl              co_dtl
  1012        buy            no_buy         no_buy         Interested         Found Cheaper       Other Faults
  5278        buy            buy                           Found Cheaper      
  1572        no_buy         buy            buy            No Interest        Cheap               Cheap
  6896        no_buy         no_buy         no_buy         Other Faults       Cheap               Found Cheaper
  </code></pre>
  <p>Now, for each item_id <strong>Reg_cst_dtls</strong>, I need to see if ca_brd_dec match Cust_dec then ca_dtl should also match Cust_det and a new field ca_resp should be equal to ca_dtl else null.
  Similarly, if ne_brd_dec match Cust_dec then ne_dtl should also match Cust_det and ne_resp should be equal to ne_dtl else null
  And if co_brd_dec match Cust_dec then co_dtl should also match Cust_det and co_resp should be equal to co_dtl else null. Expected results as below.</p>
  <pre><code>item_id     ca_brd_dec     ne_brd_dec     co_brd_dec     ca_dtl             ne_dtl              co_dtl              ca_resp         ne_resp             co_resp
  1012        buy            no_buy         no_buy         Interested         Found Cheaper       Other Faults        Interested      Found Cheaper       Other Faults
  5278        buy            buy                           Found Cheaper      
  1572        no_buy         buy            buy            No Interest        Cheap               Cheap               No Interest
  6896        no_buy         no_buy         no_buy         Other Faults       Cheap               Found Cheaper       Other Faults    cheap               Found Cheaper
  </code></pre>
  <p>Can anyone help with how this can be achieved in Hive ?</p>
  <p>Thanks...!</p>