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 · 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 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 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: 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 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?