title: How to reduce number of joins?
body: <p>I have to left join two tables where from right table I need some columns On a join condition of three columns, and some columns on join condition of two similar but one different column   (again 3 columns) and some columns on join condition of one column (which is one of the non-matching columns in previous joins).</p>
  
  <p>Let me explain by example</p>
  
  <p>Table A has columns a1, a2, a3, a4, a5
  Table B has columns b1, b2, b3, b4, b5, b6, b7</p>
  
  <p>Now I need </p>
  
  <ul>
  <li>a1,</li>
  <li>a2,</li>
  <li>a3,a4,</li>
  <li>b1 when a2=b2, a3=b3, a4=b4,</li>
  <li>b6 when a2=b5, a3=b3, a4=b4,</li>
  <li>b7 when a2=b2</li>
  </ul>
  
  <p>Now How can I achieve this without joining the tables multiple times, Or as less times as possible. With CASE WHEN THEN structure or anything else. The queries are for Hive but most of sql features are supported. Hive has different optimization techniques but sql guys are welcome.</p>
  
  <p>Thanks in advance for your effort.</p>