选择用户不是任何人的朋友 [英] Select the users are not friend of anyone

问题描述

我有这两个表:users 和朋友(friend_status = 1 表示请求已发送，friend_status = 2 表示他们是朋友).现在我想选择所有用户都不是任何人的朋友.怎么做?

Hi, I have these two tables: users and friends (friend_status = 1 means the request is sent, friend_status = 2 means they are friends). Now I want to select all users are not friend of anyone. How to do?

假设当前用户是 1.我试过这个，但似乎是错误的.因为它只显示发送请求但尚未确认的用户.

Assuming the current user is 1. I tried this but it seems to be wrong. Because it just shows the users who sent request but not being confirmed yet.

SELECT user_id, name, email
                FROM
                (
                    SELECT user_id, name, email
                    FROM users u INNER JOIN friends f ON u.user_id = f.sender
                    WHERE f.receiver = 1 AND friend_status <> 2
                    UNION
                    SELECT user_id, name, email
                    FROM users u INNER JOIN friends f ON u.user_id = f.receiver
                    WHERE f.sender = 1 AND friend_status <> 2
                ) T
                LIMIT 0, 10

推荐答案

SELECT
    a.user_id,
    a.name,
    a.email
FROM
    users a
LEFT JOIN
    friends b ON 
        a.user_id IN (b.sender, b.receiver) AND
        b.friend_status = 2
WHERE
    b.friend_id IS NULL

在这里，我们要LEFT JOIN users 和 friends 表，条件是用户已经发送或接收到请求并且友谊被接受.如果用户不满足这两个条件，则加入的 friends 表中的值将是 NULL.

Here, we want to LEFT JOIN the users and friends tables on the condition that the user has either sent or received a request and that the friendship was accepted. If the user didn't meet both critera, then values in the joined friends table will be NULL.

现在我们想要不满足加入条件的用户，所以我们用 WHERE b.friend_id IS 过滤掉满足条件的用户NULL，这将选择在朋友表中(在发送者/接收者字段中)没有任何相应条目的用户OR有相应条目但是他们都没有 friend_status 的 2.

Now we want the users that didn't meet the join criteria, so we filter out the ones that met the criteria with WHERE b.friend_id IS NULL, which will select users who either don't have any corresponding entries in the friends table (in sender/receiver fields) OR have corresponding entries BUT none of them having a friend_status of 2.

这篇关于选择用户不是任何人的朋友的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！