Sql server: Join two tables and select multiple column values with subquery

Table OpenPO

|      Shopping_Cart_No    |     Goods_Recipient_Emp_ID      |       accassgnmtownerid|
| -------------------------|---------------------------------|------------------------|
|    1001958413            |       160213                    |       65658            |
|    1001661570            |       61875                     |       61855            |

Table Employee

|      employee_number|             Email                    |       
|:--------------------|--------------------------------------|
|    160213           |       Quentin_Walker@gmail.com       |
|    61875            |       Mihaela_Balseanu@gmail.com     | 
|    65658            |       siva@gmail.com                 |
|    61855            |       mohan@gmail.com                |

I have two tables called OpenPO and Employee table, both tables are linked with employee_number column.
I wanted to display data mentioned below
Expected Result

|Goods_Recipient_Email| Goods_Recipient_Emp_ID| accassgnmtownerid|accassgnmtownerid_Email | 
|:--------------------------------|:----------------:|:-----------------:|:-------------|
|    Quentin_Walker@gmail.com     | 160213    | 65658  | siva@gmail.com    |
|    Mihaela_Balseanu@gmail.com   | 61875     | 61855  | mohan@gmail.com   |

Tried with left join and able to compare and select only one email
column Goods_Recipient_Emp_ID or op.accassgnmtownerid


    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  

Tried with subquery for accassgnmtownerid_Email, but it didn’t worked out.
can we apply subquery for accassgnmtownerid_Email or any other solution?

>Solution :

You need to join the table employee twice, using two different aliases.

For example:

select
  r.email as Goods_Recipient_Email,
  o.Goods_Recipient_Emp_ID,
  o.accassgnmtownerid,
  a.email as accassgnmtownerid_Email
from openpo o
join employee r on r.employee_number = o.Goods_Recipient_Emp_ID
join employee a on a.employee_number = o.accassgnmtownerid

Leave a Reply