将表联接两次，每个表具有相同字段的两个不同值

Question

我有一个记录，它有两个关联的人员记录。人员记录是卖家(人员代码=20(和客户(人员代码=1(。我想要卖家和客户所在的城市。我原以为它会直截了当，但似乎无法发挥作用——我没有得到任何结果。Folderpeople表只是将我的文件夹与我的人员关联起来。

select f.folderid, f.foldername, fp.peoplecode, 
p.addrcity as clientcity, p2.addrcity as sellercity 
from folder  f
join folderpeople fp on fp.folderid = f.folderid 
join people p on fp.peopleid = p.peopleid and fp.peoplecode = 1 
join people p2 on fp.peopleid = p2.peopleid and fp.peoplecode = 20 ; 

Answer 1

问题是您只加入了一次folderpeople表，因此永远无法获得任何行。试试这个：

select f.folderid, f.foldername, fp.peoplecode, 
p.addrcity as clientcity, p2.addrcity as sellercity 
from folder  f 
join folderpeople fp1 on fp1.folderid = f.folderid 
join people p on fp1.peopleid = p.peopleid and fp1.peoplecode = 1 
join folderpeople fp2 on fp2.folderid = f.folderid 
join people p2 on fp2.peopleid = p2.peopleid and fp2.peoplecode = 20

此外，我会使用外部联接，以防每个文件夹都没有卖家和客户。

Answer 2

试试这个，让我知道它是否有效。

with sellers as
(select peopleid, addrcity from people where peoplecode = 20),
clients as 
(select peopleid, addrcity from people where peoplecode = 1)
select f.folderid, f.foldername, fp.peoplecode, 
c.addrcity as clientcity, s.addrcity as sellercity 
from folder  f
join folderpeople fp on fp.folderid = f.folderid 
join sellers s on fp.peopleid = s.peopleid 
join clients c on fp.peopleid = c.peopleid; 

Answer 3

我怀疑您只需要left joins:

select f.folderid, f.foldername, fp.peoplecode, 
p.addrcity as clientcity, p2.addrcity as sellercity 
from folder f join
folderpeople fp 
on fp.folderid = f.folderid left join
people p
on fp.peopleid = p.peopleid and
fp.peoplecode = 1 left join
people p2
on fp.peopleid = p2.peopleid and fp.peoplecode = 20 ; 

由于fp.peoplecode不能同时为同一行中的"1"one_answers"20"，因此没有得到任何行。

这应该可以解决没有行的问题。它可能仍然不是您想要的结果集的结构。如果不是这样，请用适当的样本数据和所需的结果询问另一个问题。

Answer 4

我跳枪了——Randy的回答给出了结果——但给了我两排。但它为我指明了正确的方向。当我将Randy的解决方案与mdem7的解决方案相结合时，我发现了一个有效的解决方案。

with sellers as (select fp.folderid, p.peopleid, fp.peoplecode, p.addrcity, 
p.addrpostal from folderpeople fp , people p where fp.peopleid = p.peopleid and 
fp.peoplecode = 20), 
clients as 
(select fp2.folderid, p2.peopleid, fp2.peoplecode, p2.addrcity, p2.addrprovince, 
p2.addrpostal, p2.namelast, p2.namefirst from folderpeople fp2, people p2  where 
fp2.peopleid = p2.peopleid and fp2.peoplecode = 1)
select c.namelast as ClientLast, c.namefirst as ClientFirst, f.issuedate, 
c.addrcity as "Client City", s.addrcity as "Seller City"
from folder  f
left join sellers s on f.folderid = s.folderid  
left join clients c on f.folderid = c.folderid;