Join two table with inner join and two external keys

Question

I have two table:

Table Utenti:

Table Social:

I want this result:

Federica - Luca 0.1
Federica - Vincenzo 0.6
Federica - Silvia 0.3
...
Silvia - Vincenzo 0.5

How do I do the inner join between the two table recovering both the Username?

I tried this:

SELECT * 
FROM   Utenti 
INNER  JOIN Social 
         ON Utenti.ID_UT = Social.ID_UT1 
           AND Utenti.ID_UT = Social.ID_UT2

Answer 1

Close...use social as your starting point and join to the Utenti table twice, once for each ID.

 SELECT u1.username, u2.username,social.val
 FROM social 
 INNER JOIN utenti u1 ON U1.ID_UT=Social.ID_UT1
 inner join utenti u2 ON U2.ID_UT=Social.ID_UT2

You can join the same table multiple times, as long as you specify an alias (the u1 and u2 here).