Join two tables to find friends

Question

I have a student table with Id's and names and a Friends table with Id's and Friend Id's. I want to join these two tables and find Student's Friend.

For example, Ashley's friend is Samantha, Samantha's friend is Julia, ...

Students
 ____________
|Id|Name     |
|--|---------|
|1 |Ashley   |
|2 |Samantha |
|3 |Julia    |
|4 |Scarlet  |
 ------------

Friends
 ____________
|Id|Friend_Id|
|--|---------|
|1 |2        |
|2 |3        |
|3 |4        |
|4 |1        |
 ------------

I wrote this query to find the Friend Id's

SELECT s.name,f.Friend_Id FROM Students s
JOIN Friends f ON s.Id = f.Id

Now how do I print the friend names, like

 ____________________
|Name    |Friend Name|
|--------|-----------|
|Ashley  |Samantha   |
|Samantha|Julia      |
|Julia   |Scarlet    |
|Scarlet |Ashley     |
 --------------------

Answer 1

Something like this. Use two LEFT JOIN.

SELECT s.`name` AS 'Name', s1.`name` AS 'Friend Name'  FROM friends f
LEFT JOIN students s ON f.`id`=s.`id`
LEFT JOIN students s1 ON f.`friend_id`=s1.`id`

Result