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