Join Table Twice Each With Two Different Values for Same Field

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 ; 

3 answers

  • answered 2018-11-08 00:08 mdem7

    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 2018-11-08 01:36 Randy Fair

    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.

  • answered 2018-11-08 03:17 Gordon Linoff

    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.