Need help with select query

[waynew]

I'm working on a social network at the moment that allows users to do a number of things. This includes adding each other as friends.

 

The problem is based around this friends system.

 

The table looks a little like this:

 

friendship_id

user_id_a

user_id_b

confirmed

 

When a user sends a friend request, his or her user id is inserted into user_id_a and the person who was sent the friend request has his or her id inserted into user_id_b. When user_b accepts, the column confirmed is updated and set to 1.

 

Now the problem is with selecting a persons friends. How do I select a users friends without selecting his or her data along with it? Because currently I have a query that goes along the lines of

 

Select friends.friendship_id, user.name FROM friends, user WHERE ((friendship.user_id_a = '$current_user') OR (friendship.user_id_b = '$current_user'))

 

That's a basic gist of how I'm going about doing it. Note that I have to select the name from the user table too. Otherwise I'll get everything dumped out. I have a query working at the moment, but it is far from optimal.

[Barand]

is this what you mean?

SELECT f.friendship_id, a.name as username, b.name as friendname
FROM friends f
INNER JOIN user a ON f.user_id_a = a.id
INNER JOIN user b ON f.user_id_b = b.id
WHERE f.user_id_a = '$current_user'

 

[waynew]

Hi Barand, thanks for the reply!

 

I'll see if I can work what you wrote!