How would i do this SQL query?

.NET
1

Hey,

I am developing a social networking site and need to do a select query which pulls out users friends. Now i have a table which stores all the friends. The structure is as such:-

friend_id, user_id, f_user_id, status, date_added

so lets say i am logged in with a SESSION user_id of 10 and i accept a users friend request who has a user id of 20. My table row result will look like this:-

1, 10, 20, Aceepted, 27/01/2010 06:00:24

Now i want to pull out just the friends of the user who has an ID of lets say 10, how would i do this? i have tried in the following SQL statement. Note i am using JOIN here to get out the friends image out from the users table.


SELECT hussaini_friends.user_id, hussaini_friends.f_user_id, " +
                "hussaini_friends.status, hussaini_users.user_id, hussaini_users.fname, " +
                "hussaini_users.avatar FROM hussaini_friends INNER JOIN hussaini_users ON " +
                "hussaini_friends.f_user_id = hussaini_users.user_id " +
                "WHERE hussaini_friends.status = @status AND hussaini_users.user_id = @user_id

The parameter values are:-


            dbCommandFr.Parameters.Add("@user_id", SqlDbType.Int).Value = System.Convert.ToInt32(Session["MEM_ID"].ToString());
            dbCommandFr.Parameters.Add("@status", SqlDbType.VarChar).Value = "Accepted";

But this does not work. What it is doing is pulling out the members image who is actually logged in. So if i am logged in it shows me to be my own friend. I need the query to show friends i have accepted.

Can anybody help with this?

Regards

2

AND hussaini_users.user_id = @user_id

should be

AND hussaini_friends.user_id = @user_id

3

Hey,

Thanks alot for that. It worked perfectly im surprised i didnt pick it up!

:wink:

Regards
Billy