Help with two table sql query

[shinkaku]

I'm a newbie trying to work on a database that has a group of users. I'm trying to figure out how to keep track of user friends who are also users.

The two relavent tables for this is the User table which has the id of the user and the username. The second table has the userid and friend userids. When I do a search to list a user's friends I can get the friend's id but I want to convert that to a username which is in the first table.

User
id Integer
Username VarChar(50)
etc.

Friend
id Integer
UserId Integer
FriendId Integer

I can get the below out from the following sql command:

select User.id, User.UserName, Friend.FriendId
from User, Friend
where User.id = Friend.UserId;

UserId | UserName | FriendId
------|----------|------
4 | Bill | 34
4 | Bill | 49
4 | Bill | 51

I want this:
UserId | UserName | FriendId
-------|----------|------
4 | Bill | Jane
4 | Bill | Kate
4 | Bill | Homer

The only way I can think of doing this is to pipe the output of the orignial output to another sql command to get what I want. I don't even know if this is possible. I'm sure there's an easier way though.

-Shinkaku

[mattyblah]

Try this:

SELECT u.id AS userid, u.username, f.friendid, u2.username AS friendname FROM user u INNER JOIN friend f ON u.id = f.userid INNER JOIN user u2 ON f.friendid = u2.id

Hope that helps.