Join Table Twice Each With Two Different Values for Same Field

Question

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 ;

score 1 · Answer 1 · answered Nov 08 '18 at 01:36

The problem is you joined to the folderpeople table only once so you can never get any rows. Try this:

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

Also, I would use outer joins just in case you didn't have both a seller and a client for every folder.

I jumped the gun - this gave results - but gave me two rows for everyone. But it pointed me in the right direction, when I combined this with mdem7's suggestion below, I found a solution that worked. — Connie, Nov 09 '18 at 15:45

score 0 · Answer 2 · answered Nov 08 '18 at 00:08

0

Try this let me know if this works.

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;

answered Nov 08 '18 at 00:08

demircioglu

3,069
1
15
22

Hmmmm..... this one still gives no results. But - I like defining the tables in a with - I'm going to try combining this with Randy's suggestion, above. – Connie Nov 08 '18 at 13:16

score 0 · Answer 3 · answered Nov 08 '18 at 03:17

I suspect that you simply need 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 ;

You get no rows because fp.peoplecode cannot be both "1" and "20" in the same row.

This should fix the problem that you have no rows. It still may not be the structure of the result set that you want. If this is not the case, ask another question with appropriate sample data and desired results.

score 0 · Accepted Answer · answered Nov 09 '18 at 15:51

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;

Join Table Twice Each With Two Different Values for Same Field

4 Answers4