data from two tables. delete query

Question

hi i am making social networking website

i want to retrieve and delete data from my friend's table.. that is profile table and friends table

friend table has field (friend_Id, my_Id, fstatus etc)

and profile table all fields of profile like profile image, firstname,lastname, city etc

for select command i got help from one social networking example which is like this way :

<asp:SqlDataSource ID="friendsq" runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>"

SelectCommand="select * from profile where profileId IN (Select my_Id as profileId from friends where friend_Id=@profileId AND fstatus=1 UNION select friend_Id as ProfileId from friends where my_Id=@profileId AND fstatus=1)">

<asp:ProfileParameter Name="profileId" PropertyName="profile_Id" />

</SelectParameters>

</asp:SqlDataSource>

this will display all friends of user who is "logged in"

now i am confuse about the delete query...

how we can specify delete query to delete friend from friend's table...

Sushta Guthnar · Answer

you have wonderful ideas haveing much creativity. you can use my correct answers here. make new statements witch make you very happy like this delete from profile where profileId IN (Select my_Id as profileId from friends where friend_Id=@profileId AND fstatus=1 UNION select friend_Id as ProfileId from friends where my_Id=@profileId AND fstatus=1) congratulations on your projects of social networks

Sushta Guthnar · Answer

You are haveing two conflicts of idea. Mine is table oriental if you are to deleteing the column. Composite keys are the most best for bettering the table of profile. I want you to be suffering from happinesses Sushta Marking me as answering i will be happy inside.

Amit Choudhary · Answer

hi diya I got your problem... and i got a tricky solution for it. here you should treat the my_id and friend_id as a composite key. Always look for the combination of my_id and friend_id. Try a count query with my_id=uid and friend_id=fid if it returns 0 then Interchange the values like my_id=fid and uid use these values like this and delete from there database based on this condition. Please mark as answer if it helps.

Dipa Ahuja · Answer

Dipa Ahuja · Answer

to Sushta Guthnar hi.. thanx for reply.. bt what my requirement is that data are displayed actually from friends table.. i use profile table becoz friends table contains fields like(friend_Id my_Id fstatus) bt to display friend's firstnamelastnameprofile_Image we need profile table.. now at the time of deleting the data from friends table we have to use delete from friends not from profile... to AMIT hi.. this is not that much easy... the my_id field wont be always the user who is online or whose friends are displaying because on inserting records on friends table... my_Id will be the id of person who will send request..! n while displaying friends data in some records the my_Id=profileId(if user has sent request ->reciever)) and in some friend_Id=profileId (if user has accept the friend Request ->acceptr) thats being so confusing for me...

Amit Choudhary · Answer

hi diya Fetch your friend_id from user's friend list. and use it to delete from friend table my_id will be the currently logged in user. now apply the sql delete command using these parameters Delete from FriendTable where friend_id=fid and my_id=uid; Hope it helps. please mark as answer if it helps.