LucasP LucasP - 11 days ago 7
SQL Question

Replacing a foreign key by a primary key

I want to know how to replace a foreign value by a primary key value when both columns are of the same type.

Table1: Users:

user_id
user_name


Table2: Followers:
user_name
followed_user_name


(Assume names are unique)

I want to create a query to obtain the same thing as Table2 but changing each
user_name
by the
id
and the
follower_user_name
by the
id
. I am confused because both are of the same type.

What I have so far:

SELECT
table1.user_id,
table2.follower_user_name #This needs to be replaced
FROM table2
INNER JOIN table1
ON table2.user_name = table1.user_name


PS: this is the database I am working with, I didn't design it. Not sure why they just didn't use the ids instead in the follower table.

Answer

You can join followers on users twice, once for the user name and once for the followed user's name:

SELECT u1.id, u2.id
FROM   followers f
JOIN   users u1 ON u1.user_name = f.user_name
JOIN   users u2 ON u2.user_name = f.followed_user_name