I am sstruggling to make a query to join the corresponding replacing the IDs. I have two tables USERS and CONNECTIONS.
Users:
x
user_id first_name-------------------- 3 Jim 4 Pam 5 MichaelConnections:
user_id friend_id-------------------- 3 4 4 3 4 5 5 4In Connections both columns includes the user_id from Users to represent the connection between the people. I need the result like below:
user friend----------------Jim PamPam JimPam MichaelMichael PamAdvertisement
Answer
Joining Connections table with Users tables give the expected result.
By using U1.user_id = C.user_id to get the user and using U2.user_id = C.friend_id to get the friend values.
SELECT U1.first_name AS user, U2.first_name AS friendFROM Connections CJOIN Users U1 ON U1.user_id = C.user_idJOIN Users U2 ON U2.user_id = C.friend_idoutput:
user friend--------------------Jim PamPam JimPam MichaelMichael Pam