如何计算两个用户之间的共同朋友并在PHP中列出它们

Question

I know I am not asking a new question but the problem is that I am not getting the things right... Please help me...

I have the following tables:

users(user_id, full_name, course etc.)

friends(user_id (sender), friend_id (receiver), status)

status decides the relationship, if status=1 it means the request is pending while status=2 means the sender and receiver are friends).

I just want to count the MUTUAL friends between the current logged-in user and the other user (whom the current logged-in user visits the profile).

And also, I want to list them separately.

Answer 1

SELECT * FROM
( /*    Dervived table for friends of userid 1 */
SELECT * FROM friends where user_id=1) fsof1
JOIN 
( /*    Dervived table for friends of userid 2 */
SELECT * FROM friends where user_id=2) fsof2
ON fsof1.friend_id = fsof2.friend_id