title: Sql server: Join two tables and select multiple column values with subquery
body: <p>Table <code>OpenPO</code></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">Shopping_Cart_No</th>
  <th style="text-align: center;">Goods_Recipient_Emp_ID</th>
  <th style="text-align: center;">accassgnmtownerid</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">1001958413</td>
  <td style="text-align: center;">160213</td>
  <td style="text-align: center;">65658</td>
  </tr>
  <tr>
  <td style="text-align: center;">1001661570</td>
  <td style="text-align: center;">61875</td>
  <td style="text-align: center;">61855</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Table <code>Employee</code></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">employee_number</th>
  <th>Email</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">160213</td>
  <td>Quentin_Walker@gmail.com</td>
  </tr>
  <tr>
  <td style="text-align: center;">61875</td>
  <td>Mihaela_Balseanu@gmail.com</td>
  </tr>
  <tr>
  <td style="text-align: center;">65658</td>
  <td>siva@gmail.com</td>
  </tr>
  <tr>
  <td style="text-align: center;">61855</td>
  <td>mohan@gmail.com</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I have these two tables, both are linked by the <code>employee_number</code> column.</p>
  <p>I want to display data as shown below .</p>
  <p>Expected result</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Goods_Recipient_Email</th>
  <th style="text-align: center;">Goods_Recipient_Emp_ID</th>
  <th style="text-align: center;">accassgnmtownerid</th>
  <th style="text-align: left;">accassgnmtownerid_Email</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Quentin_Walker@gmail.com</td>
  <td style="text-align: center;">160213</td>
  <td style="text-align: center;">65658</td>
  <td style="text-align: left;">siva@gmail.com</td>
  </tr>
  <tr>
  <td>Mihaela_Balseanu@gmail.com</td>
  <td style="text-align: center;">61875</td>
  <td style="text-align: center;">61855</td>
  <td style="text-align: left;">mohan@gmail.com</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Tried with left join and able to compare and select only one email column Goods_Recipient_Emp_ID or op.accassgnmtownerid</p>
  <pre><code>SELECT 
      op.Goods_Recipient_Emp_ID, op.accassgnmtownerid, 
      te.Email AS accassgnmtownerid_Email
  FROM 
      OpenPO op
  LEFT JOIN 
      Employee te ON te.Employee_Number = op.Goods_Recipient_Emp_ID  
  </code></pre>
  <p>Tried with subquery for <code>accassgnmtownerid_Email</code>, but it didn't work out.</p>
  <p>Can we apply subquery for <code>accassgnmtownerid_Email</code> or is there any other solution?</p>