CODEWITHSUNDEEP
mysqlsql
emeraldhieu
emeraldhieu

Reputation: 9439

Select the users are not friend of anyone

enter image description here

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?

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

Upvotes: 0

Views: 262

Answers (5)

Sudhakar B
Sudhakar B

Reputation: 1573

Try this

U.User_ID,u.Name
From Users AS U
Where User_ID NOT IN 
(Select Sender From Friends Where Status=2) AND User_ID NOT IN 
(Select Receiver From Friends Where Status=2)

Upvotes: 0

Jonas T
Jonas T

Reputation: 3077

All users without any friend?

SELECT * FROM users 
WHERE user_id NOT IN(SELECT sender FROM friends WHERE friend_status=2)
AND user_id NOT IN(SELECT receiver FROM friends WHERE friend_status=2)

Upvotes: 0

user149341
user149341

Reputation:

Another solution. Note that this may not perform as well as Zane's solution, but it does have the advantage of being less arcane:

SELECT * FROM users AS u
  WHERE NOT EXISTS(
    SELECT * FROM friends
      WHERE friend_status = 2
      AND (sender = u.user_id OR receiver = u.user_id)
  );

Should be simple enough to read: It selects every user that doesn't have any inbound or outbound friend relationships.

Upvotes: 2

Zane Bien
Zane Bien

Reputation: 23135

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

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.

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.

Upvotes: 5

Sean Johnson
Sean Johnson

Reputation: 5607

SELECT user_id,name,email
FROM users
WHERE user_id NOT IN (
    SELECT sender FROM friends WHERE friend_status<>2
    UNION
    SELECT receiever FROM friends WHERE friend_status<>2
)

If anyone can post a more optimized solution than this, I'd like to see it!

Upvotes: 0

Related Questions