Hi i am facing problem regarding the structure of a table for storing the all the friends list of an user. In the below table which i have used if user 84 has requested for friendship with user 70 & 74 then user 84 is friends are 70 & 74 and vice versa. Similarly user 77 with 84.
But the problem is that if i want to find out all the friends and their detail of user 84 i.e. of user (70,74,77).
Should i enter duplicate record like image 2 Ex: if user 84 send friend request to user 70 when user 70 accept the request then another row will be inserted like user_id 70 & friend_id 84.
OR is there any mysql query to join with users table based on the column value of the friends table. Like if user_id is 84 it will join based on the friend_id and if friend_id is 84 then it will join with user table by using the user_id column.
I assume that in your system friendship is symmetrical, i.e. if A is friends with B that implies that B is also friends with A. In which case, I suggest that you change your Friend table's columns to reflect the fact that the two people have equal status in the relationship e.g.
With that in place, you could do a query something like this
SELECT * FROM User WHERE User.UserId IN ( (SELECT User1_Id FROM Friend WHERE User2_Id = MY_USER_ID) UNION (SELECT User2_Id FROM Friend WHERE User1_Id = MY_USER_ID) )
This uses a sub-select rather than a join, plus a union to get a single list of friends' user ids depending on which way around the users are listed in the Friend table. Documentation of sub-query and union.
It's probably worth keeping it like this, rather than simplifying the code by having 2 rows in your Friends table for each relationship - (A, B) and (B, A). If you have N users the Friends table will have up to N*(N-1) rows if you duplicate and half that if you don't.