MySQL how get users who are not my friends-CodePudding

could someone help me, I need to get users who are not my friends I have the following tables

users

id	`user`
1	Name1
2	name2

and the table friends:

id	`id_user`	`id_friends`
1	2	3
2	5	4

I try something like that, but in some queries it returns the ones that I already have added

Select  u.*
from    users u left join friends f
          on f.id_user = 2 
          and f.id_friends = u.id
where  u.id <> 2
and    f.id_friend is null

CodePudding user response：

One way is using NOT EXISTS to return all users not already "friended". That includes users not already in the friends table.

SELECT u.*
FROM   users u 
WHERE  u.id <> 2
AND    NOT EXISTS (
       SELECT NULL
       FROM   friends f 
       WHERE ( f.id_friend = u.id AND f.id_user = 2 )
       OR    ( f.id_friend = 2 AND f.id_user = u.id )

)

Results:

id | user 
-: | :----
 1 | Name1

db<>fiddle here