query from two tables

Question

hi i want to get data like friendAddRequests that are shown on our home page when we sign in any social networking website like orkut.
fields are
table:profile
1.profile_Id 2.pimage
table:friends
1.friendid 2.myid
from friend table i want to get friendid like (select friendid,myid from friends where myid=profile.myid)
where profile.myid is profile field of web.config file
n this data i want to load in gridview  bt after getting "friendid" from friends i want to get profile_image of friendid from "profile"  table where friendid(in profile table is profile_Id)..

Kirtan Patel · Accepted Answer

select p.profile_id,p.pimage,f.friendid from profile p inner join friend f on f.myid=p.profile_id

Dipa Ahuja · Answer

hi i use this way n it worked.. thank you.. i marked your answer
"
SelectCommand="SELECT p.profileId, p.pimage, f.friend_Id, f.msg, p.fname, p.lname FROM profile AS p INNER JOIN friends AS f ON f.my_Id = p.profileId where f.friend_Id=@profileId AND f.fstatus=0">
bt one more problem.. the when user accept the request now the person is added to his/her friendlist.. n now i want to load it in datalist. its not working one side..
receiver get that person in his/her friendlis bt sender's friendlist i cant see i used this way:
"
SelectCommand="SELECT p.profileId, p.pimage, f.friend_Id, f.msg, p.fname, p.lname FROM profile AS p INNER JOIN friends AS f ON f.my_Id = p.profileId where (f.my_ID = p.ProfileId AND f.friend_Id=@profileId  AND f.fstatus=1) OR (f.my_ID = @profileId  AND f.fstatus=1) ">
its being really confusing for me... can you help ?
for this help i marked your answer

Dipa Ahuja · Answer

see my insert query in "Friends" table is :
ProfileCommon p;
protected void Page_Load( object sender, EventArgs e)
{
p= this .Profile;
pid = Convert .ToInt32(Request.QueryString[ "pid" ].ToString());
}
protected void btnsend_Click( object sender, EventArgs e)
{
SqlCommand comm = new SqlCommand ( "insert into friends(fid,friend_Id,my_Id,msg,fstatus) values (@fid,@friend_Id,@my_Id,@msg,@fstatus)" , fconnection);
comm.Parameters.AddWithValue( "@fid" , fid);
comm.Parameters.AddWithValue( "@friend_Id" , pid);
comm.Parameters.AddWithValue( "@my_Id" , p.profile_Id);
comm.Parameters.AddWithValue( "@msg" , txtmsg.Text.ToString());
comm.Parameters.AddWithValue( "@fstatus" , 0);
comm.ExecuteNonQuery()
}
and the solution that you have suggested i used this way:.
asp : SqlDataSource ID ="f" runat ="server"
ConnectionString =" "
SelectCommand ="SELECT p.profileId, p.pimage, f.friend_Id, f.msg, p.fname, p.lname FROM profile AS p INNER JOIN friends AS f ON f.my_Id = p.profileId">
asp : SqlDataSource >
bt the user who send request to any user the sender's request sent to each user's home page means it gives friend request on each user's home page
i want that myid should be profile parameter like this way:
so how to do?