sqlself-join

How to return name, given an ID Number from same dataset?

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

Solution

  • 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;