I have a record that has two people records associated with it. The people records are sellers (peoplecode = 20)and clients (peoplecode = 1). I want the city of both the seller and the client. I thought it would be straight forward, but cannot seem to get it to work - I get no results. The Folderpeople table just relates my folder to my people.
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 ;
I jumped the gun - Randy's answer gave results - but gave me two rows for everyone. But it pointed me in the right direction. When I combined Randy's solution with mdem7's solution, I found a solution that worked.
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;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments