MySQL query to show mutual friend suggestions

I am developing a website and have a query to show mutual friend suggestions, however the query isn't working perfectly. These friend suggestions will each have a button that when clicked will send a friend request to the user. The query that I have now shows mutual friend suggestions but is also displaying members who the user has already sent a request to. I do not want these people in the list, since a request has already been sent from the user viewing this list.

`friends` table structure with sample data


id, user_id, friend_id, status, date

149 1253343 1243522 0 1567894334

150 1243522 1253343 1 1567894334

151 1262117 1441712 2 1578241277

152 1441712 1262117 2 1578241277

When a user requests a friendship, two rows are inserted into the database, one for the requester and one for the requested. The requester row has a status of 1 while the requested row has a status of 0. Any status 2 means a confirmed friendship between two users.

Here is the current query that needs to be modified to not show rows with a 0 or 1. In other words it shouldn't return users who have already sent or received a request from the user. The query should only return users who have not been sent a request yet from the user or have not sent one to this user. This database query stuff is a little over my head. I'm not sure if there is a query generator program that would help me with these complicated queries but for now I am requesting assistance from the developer community. Good luck!



COUNT(*) as relevance,

GROUP_CONCAT(a.user_id ORDER BY a.user_id) as mutual_friends


friends a


friends b

ON (

b.friend_id = a.user_id

AND [login to view URL] = 2

AND b.user_id = ".$_SESSION['user_id']."



friends c



c.friend_id = a.friend_id

AND [login to view URL] = 2

AND c.user_id = ".$_SESSION['user_id']."



[login to view URL] = 2


c.user_id IS NULL


a.friend_id != ".$_SESSION['user_id']."





Skills: MySQL, Software Architecture

See more: edit csv mysql query, asp mysql query, 2013 lost connection mysql query, mysql query report, php mysql query table, mysql query record bash, mysql query csv file, write mysql query, optimize mysql query software, mysql query php table css, php mysql query format table, mysql query search, cacti custom mysql query, google map mysql query results, php mysql query category drop, unowned service requests help desk template sharepoint, mysql query remote database, mysql query find children parent, write javascript function mysql query

About the Employer:
( 4 reviews ) Bethel, United States

Project ID: #21182606

Awarded to:


Hey, I will rewrite or modify your query to work correctly. Right now I’m on mobile so I can’t test your query but I have go through your table structure and query you have written. If I will get this job, then I wi More

$25 USD in 1 day
(8 Reviews)

3 freelancers are bidding on average $21 for this job


Hi, are you looking for an Real MySQL Expert? If yes, you're in RIGHT place and WELCOME! High-quality and Fast-delivery is promised! As a highly skilled full stack developer, I have rich experience in MySQL. I am very More

$20 USD in 1 day
(57 Reviews)

I can start the work as soon as you approve me the same and submit the deliverables in agreed timeline with quality and optimized coding pattern. I can do the optimized query as per your requirements as I have implemen More

$19 USD in 1 day
(2 Reviews)