MySQL 获取还不是好友的用户 [英] MySQL fetch users who are not yet friends

问题描述

大家好，我是新来的，我需要你的帮助来获取还不是朋友的用户.

Hello guys I'm new to this, I need your help to fetch users who are not yet friends.

这是我的桌子:

  Users 
  id    username   
  1     kyle
  2     jane
  3     jim
  4     carla

  Friends
  id    username  friend
  1     kyle      jane
  2     jane      kyle
  3     kyle      jim
  4     jim       kyle

这是我迄今为止尝试过的:

This is what I've tried so far:

  SELECT username
  FROM users AS u
  WHERE NOT EXISTS (SELECT * FROM friends AS f
              WHERE (f.username = 'kyle')
             )

但是我没有检索到任何结果.它应该显示 1 条记录，那就是 carla.有任何想法吗?我很乐意感谢您的帮助.谢谢.另外，您能否为此提出更稳健的方法?

But I get no results retrieved. It should display 1 record and that is carla. Any ideas? I would gladly appreciate your help. Thanks. Also, can you suggest much more robust approach to this?

推荐答案

经过长时间的寻找解决方案，这就是我得到的:

After long hours of finding solution, this is what I've got:

 SELECT * FROM users AS u 
 WHERE u.username NOT IN 
 (
     SELECT f.username FROM friends as f
     WHERE f.username = 'kyle'
     OR f.friend = 'kyle'
 )

我希望这也能帮助其他人.

I hope this will help others as well.

这篇关于MySQL 获取还不是好友的用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！