Table OpenPO

Table Employee

I have these two tables, both are linked by the employee_number column.

I want to display data as shown below .

Expected result

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 work out.

Can we apply subquery for accassgnmtownerid_Email or is there any other solution?

CodePudding user response：

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

CodePudding user response：

Other than joining the tables for each Email, you can also use a correlated subquery for each, such as

select 
  (select Email from Employee e where e.employee_number = po.Goods_Recipient_Emp_ID) Goods_Recipient_Email,
  po.Goods_Recipient_Emp_ID,
  po.accassgnmtownerid,
  (select Email from Employee e where e.employee_number = po.accassgnmtownerid) accassgnmtownerid_Email
from OpenPO po;