SQL JOIN Twice on same column

Question

Say I have a table [public].[ids] with a mapping of customer IDs

public.ids

old_id | new_id
===============
   100 |   1000
   101 |   1001
   102 |   1002

I also have a referrals table where I log one row when a customer refers another like so:

public.referrals

referring_id | referred_id
==========================
         100 |         101
         101 |         102

I want to know what this table looks like using the new ids. Which means I will have to join on the referrals table twice. Once for the referring_id and once for the referred_id. Ultimately, I want a table that looks like this:

result

referring_new_id | referred_new_id
==================================
            1000 |            1001
            1001 |            1002

How can I accomplish this with the custom naming of these new columns? I'm using postgresql, but any flavor of SQL would be helpful.

SqlZim · Accepted Answer · 2017-10-31 22:27:45Z

5

Using two joins:

select 
    i1.new_id as referring_new_id
  , i2.new_id as referred_new_id
from referrals r
  inner join ids i1 on r.referring_id = i1.old_id
  inner join ids i2 on r.referred_id  = i2.old_id;

rextester demo: http://rextester.com/VMJHE81524

returns:

+------------------+-----------------+
| referring_new_id | referred_new_id |
+------------------+-----------------+
|             1000 |            1001 |
|             1001 |            1002 |
+------------------+-----------------+

answered Oct 31, 2017 at 22:27

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3685285 Over a year ago

duh! How come I didn't think of this...?

Collectives™ on Stack Overflow

SQL JOIN Twice on same column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related