Reputation: 9439
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
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
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
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
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
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