CODEWITHSUNDEEP
mysqlsql
emeraldhieu
emeraldhieu

Reputation: 9439

Select the users are not friend of a specific user

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 a specific user. How to do?

Assuming the current user is 1. I tried this SQL. It works but it's too long and slow. The first selects all users sent request to user1 but not accepted. The second selects all users receive request from user1. The third and the fourth selects all users is not in "friends" table.

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
            UNION
            SELECT u.user_id, u.name, u.email
                    FROM users u LEFT JOIN friends f ON u.user_id = f.sender
                    WHERE f.receiver IS NULL
                    GROUP BY user_id
                UNION 
            SELECT u.user_id, u.name, u.email
                    FROM users u LEFT JOIN friends f ON u.user_id = f.receiver
                    WHERE f.sender IS NULL
                    GROUP BY user_id
                ) T
        GROUP BY user_id

Update: Add a pic. enter image description here

Upvotes: 1

Views: 1216

Answers (4)

Zane Bien
Zane Bien

Reputation: 23125

SELECT
    a.user_id,
    a.name,
    a.email,
    b.status IS NOT NULL AS friend_status
FROM
    users a
LEFT JOIN
    friends b ON 
        a.user_id IN (b.sender, b.receiver) AND
        1 IN (b.sender, b.receiver)
WHERE
    (b.friend_id IS NULL OR b.status <> 2) AND
    a.user_id <> 1

You had asked a question previously here - "Select users who aren't friends with anyone", and I provided an answer which utilized a LEFT JOIN.

Building off of that, to select users who aren't friends with a specific user, we just simply need to add that specific user's ID to the LEFT JOIN condition (1 IN (b.sender, b.receiver).

Minor Edit: Unless the user can friend him/herself, it wouldn't make sense to also select the user who we're selecting against!! So I added WHERE a.user_id <> 1.

Upvotes: 2

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Try this query 

select
  u.user_id,
  u.name,
  u.email,
  ifnull(f.friend_status,0) as Relation
from users as u
  left join friends as f
    on f.sender = u.user_id
where u.user_id not in(select
             sender
               from friends
               where sender = 1)

Here sender = 1 means the user id = 1. You can pass user id to restrict this condition. Also status 0 means he is not friend. and 1 , 2 , 3 are according to your rules

Upvotes: 1

StevenR
StevenR

Reputation: 396

You may be able to simplify this by using something like this:

SELECT user_id, name, email
FROM
(
    SELECT u.user_id, u.name, u.email
    FROM users u LEFT JOIN friends f ON u.user_id = f.sender
    WHERE IFNULL(friend_status,0) <> 2
    GROUP BY user_id
    UNION
    SELECT u.user_id, u.name, u.email
    FROM users u LEFT JOIN friends f ON u.user_id = f.receiver
    WHERE IFNULL(friend_status,0) <> 2
    GROUP BY user_id
) T
GROUP BY user_id

The IFNULL function returns the value of the first parameter, replacing NULLs with the value of the value second parameter. In this case it means that friend_status will be treated as 0 if there is no matching friend in the friends table, which allows you to reduce the number of selects in the UNION by half.

Upvotes: 1

Marco Leogrande
Marco Leogrande

Reputation: 8458

Assuming you want to perform the query on user_id 1:

SELECT user_id, name, email
FROM users AS u
WHERE NOT EXISTS (SELECT * FROM friends AS f
                  WHERE (f.sender = u.user_id AND f.receiver = 1 AND f.friend_status = 2)
                  OR (f.sender = 1 AND f.receiver = u.user_id AND f.friend_status = 2)
                 )
AND u.user_id <> 1

The subquery fetches all the established friendship relationship in which user 1 is either the sender or the receiver. The outer query selects all users for which no such relationship exists. The user with ID 1 is excluded from the query using the last line, as, even if he cannot be friend with himself, I suppose that he should not appear in the final query result.

Upvotes: 1

Related Questions