title: Extract ang join from same column in Hive
body: <p>I have a requirement to have different data from single column from another table.</p>
  <p>Lets say I have a TAB_A with below sets of columns.
  <a href="https://i.stack.imgur.com/xY0jL.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xY0jL.png" alt="enter image description here" /></a></p>
  <p>TAB_B has one column.</p>
  <p><a href="https://i.stack.imgur.com/nIvXQ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nIvXQ.png" alt="enter image description here" /></a></p>
  <p>My expected out put should be,
  <a href="https://i.stack.imgur.com/f6qtX.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/f6qtX.png" alt="enter image description here" /></a></p>
  <p>Here col_x should have the data when code(TAB_B) = '08'
  and
  col_y should have the data when code(TAB_B) = '36'.</p>
  <p>Whatever I have tried is not working,</p>
  <pre><code>SELECT 
  a.col1, 
  a.col2, 
  a.col3, 
  a.col4, 
  substr(a.col5,13,3) as col_x,
  substr(a.col5,13,3) as col_y
  from TAB_A a 
  JOIN TAB_B b on b.code = '08'
  JOIN TAB_B b on b.code = '36'
  </code></pre>
  <p>Can anyone please suggest the best solution.</p>