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

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

Collectives™ on Stack Overflow

SQL JOIN Twice on same column

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related