I want to write a query that returns names and their best friends name from the table below:
Example FRIEND Table:
| FriendID | Name | BestFriendID |
|---|---|---|
| 1 | Abby | |
| 2 | Bob | 3 |
| 3 | Carl | 2 |
Expected Output:
| Name | Bestfriend |
|---|---|
| Abby | |
| Bob | Carl |
| Carl | Bob |
My Code so far:
SELECT A.Name, B.BestFriendID AS Bestfriend
FROM FRIEND A, FRIEND B
WHERE A.FriendID = B.BestFriendID;
The problem is the output returns the ID of the original friend:
| Name | Bestfriend |
|---|---|
| Abby | 1 |
| Bob | 2 |
| Carl | 3 |
So what am I missing? Any help would be appreciated
You're close! You need to join the tables on the BestFriendID and then select the friend's name, you forgot that - try this:
SELECT A.Name, B.Name AS Bestfriend
FROM FRIEND A
LEFT JOIN FRIEND B ON A.BestFriendID = B.FriendID;