如何将两个查询合并到一行中?

I have a situation here to get data(i.e. fname and lname) from two different table comparing their jobid, deliverymanid, pickupmanid and employeeid from job and employee table and combine it in one row.

我在这里有一个获取数据的情况。(fname和lname)来自两个不同的表，比较它们在job和employee表中的jobid、deliverymanid、pickupmanid和employeeid，并将其合并为一行。

This is the job table

这是工作表

jobid  pickupmanid  deliverymanid
-----  -----------  -------------
  1         1            2
  2         2            2

This is the employee table

这是员工表

employeeid     fname         lname
----------  -----------  -------------
    1           ABC          XYZ
    2           LMN          OPR

Here pickupmanid and deliverymanid act as a foreign keys for job table referring to employeeid in employee table.

这里的pickupmanid和deliverymanid作为工作表的外键指向employee表中的employeeid。

1 个解决方案

#1

You could join the job table on the employee table twice - once for the picker-upper and once for the deliveryman:

你可以两次加入员工桌子上的工作桌——一次为上扒手，一次为送餐员:

SELECT j.jobid, 
       p.fname AS pickup_fname, p.lname AS pickup_lname,
       d.fname AS delivery_fname, d.lname AS delivry_lname
FROM   job j
JOIN   employee p ON p.employeeid = j.pickupmanid
JOIN   employee d ON d.employeeid = j.deliverymanid