CODEWITHSUNDEEP
sqlpostgresql
user3685285
user3685285

Reputation: 6566

SQL JOIN Twice on same column

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.

Upvotes: 2

Views: 298

Answers (1)

SqlZim
SqlZim

Reputation: 38023

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 |
+------------------+-----------------+

Upvotes: 5

Related Questions