Query to get all non-friend users from single table

Question

I am currently working on SQL query for MySQL where need to find out all friends who are not my in friend list. This query statement looks simple but I am trying it from very long time and I am not succeeded in this. Below is schema for my table.

tbl_user
iUserID (int)
userFullName (varchar)

tbl_user_friend
iUserID (int)
iFriendID (int)

Here 'tbl_user' contains user's all information and in 'tbl_user_friend' contains their friends relation.

Let's say User1 send request to User2 and we have 1 record ([1,2]) in 'tbl_user_friend' and after that User2 accept this request and I am adding one more row to 'tbl_user_friend' with ([2,1]).

So now I have 2 entries in 'tbl_user_friend' ([1,2],[2,1]).

And again User1 send rquest to User3, and have 3 entries in table like below ([1,2],[2,1],[1,3])

But now I am trying to fetch all the users who are not friend of User1. So I am expecting result like ([3, User3],[4, User4],[5, User5]). Because User3 haven't accepted request yet. And in expecting result I added 2 more entries "User4" and "User5", which are the users who are not connected with User1 from "tbl_user_friend" (In other words, Users who haven't sent friend request to User1).

Expecting result = (friends who not accepted my request + friends who haven't send me friend request)

Answer 1

Try this:

SELECT iUserID FROM tbl_user
WHERE iUserID != 3
AND iUserID NOT IN (SELECT b.iFriendID AS friendID FROM tlb_user a JOIN tlb_user_friend b
ON a.iUserID = b.iUserID
WHERE a.iUserId = 3
AND EXISTS (SELECT 1 FROM tbl_user_friend
WHERE iUserID = friendID AND iFriendID = 3))

The subquery in the not in (SELECT b.iFriendID ... AND iFriendID=3)) selects all of the users (specifically the iFriendIDs which are essentially iUserIDs) who are friends with iUserID 3. Then the the rest of the query selects all of the users who are not in that list of friends we obtained from the subequery.

You can change all of the 3s in the code to the iUserID for whom you want to obtain the list of users who aren't friends.