Sql server:连接两个表并使用子查询选择多个列值

Question

表OpenPO

accassgnmtownerid6565861855

Answer 1

您需要使用两个不同的别名两次连接表employee。

例如:

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

Answer 2

除了为每个Email连接表之外，您还可以为每个Email使用相关子查询，例如

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;