MySQL - NOT IN produces unwanted result
I have tables below:
user
+-----------------------------------------------+
| user_id | username | Password | ... |
+-----------------------------------------------+
| 1 | a | *** | ... |
+-----------------------------------------------+
| 2 | b | *** | ... |
+-----------------------------------------------+
| 3 | c | *** | ... |
+-----------------------------------------------+
| 4 | d | *** | ... |
+-----------------------------------------------+
| 5 | e | *** | ... |
+-----------------------------------------------+
friends
+-----------------------------------------------+
| f_id | user_id | friend_id | ... |
+-----------------------------------------------+
| 1 | 4 | 2 | ... |
+-----------------------------------------------+
| 2 | 4 | 1 | ... |
+-----------------------------------------------+
| 3 | 4 | 5 | ... |
+-----------------------------------------------+
| 4 | 4 | 3 | ... |
+-----------------------------------------------+
I want all users to be available to add as friends (in this case, user_id
of 1
will add 3 more friends (2, 3, 5)
. However, using the following SQL statement below
I only get 1 user (4)
that can be added:
$sql = "SELECT * FROM user WHERE user.user_id NOT IN
(SELECT friends.friend_id FROM friends) AND
user.user_id <> $_SESSION['id']." ORDER BY RAND() LIMIT 5";
But this works great when I am logged in as user 4, which has no users available to add. It's a little tricky for me. Any idea would be much appreciated.
thank
source to share
Edit:
How about this: (the else clause uses an invalid id or the same id as the logged in user)
select * from users where id not in
(
select (
case
when uid = 1 then id
when fid = 1 then uid
else 0
end
) from friends where uid = 1 or fid = 1
) and id != 1 order by rand() limit 5;
http://sqlfiddle.com/#!2/12de1/62
Another way to solve this (but may not be the optimal solution):
The next union query may not be an expensive query compared to a full join between the two tables if the user has less friends than the total number of users in the system.
Also don't forget to add index to column uid
and fid
tables friends
.
select * from users where id not in
(
select id from friends where uid = 1
union
select uid from friends where fid = 1
) and id != 1 order by rand() limit 5;
source to share
How about this? null
shows where the user 4
shouldn't add the user 4
as a friend .. Infact you can filter it And FID is not null
. If you want to select to be added friends
specify a specific user according to yours session id
, then you can also cancel it in another condition :)
Updated adding another connection to make sure names are shown to friends and not user. As well as there more 4
.
Query:
select x.id, a.name, x.fid
from users a
join (
select u.id, u.name, f.fid
from users u
left join friends f
on u.id <> f.fid
and u.id <> f.uid) x
on x.fid = a.id
;
Results:
| ID | NAME | FID |
-------------------
| 1 | b | 2 |
| 1 | e | 5 |
| 1 | c | 3 |
| 2 | a | 1 |
| 2 | e | 5 |
| 2 | c | 3 |
| 3 | b | 2 |
| 3 | a | 1 |
| 3 | e | 5 |
| 5 | b | 2 |
| 5 | a | 1 |
| 5 | c | 3 |
For a specific user, for example. 1
select x.id, x.fid, a.name
from users a
join
(select u.id, f.fid
from users u
inner join friends f
on u.id <> f.fid
and u.id <> f.uid
and u.id = 1)x
on x.fid = a.id
;
| ID | FID | NAME |
-------------------
| 1 | 2 | b |
| 1 | 3 | c |
| 1 | 5 | e |
source to share
What your SQL query does: Get all users where id is not ANY friend_id, not the current user (I'm guessing).
What you want is to check if there are no entries for the friend_id and user_id pair in the friends table, so something like: $ sql =
"SELECT * FROM user WHERE user.user_id NOT IN
(SELECT friends.friend_id FROM friends WHERE friend.user_id = user.user_id) AND
user.user_id <> $_SESSION['id']." ORDER BY RAND() LIMIT 5";
source to share
Instead of using it NOT IN
with a subquery, you can try a JOIN
table friends
.
I JOIN
edited it twice. As soon as it 1
appears user_id
and once when it 1
appears friend_id
. Then I got all the users who are not friends with 1
or are 1
.
SELECT user.user_id,user.username
FROM user
LEFT JOIN friends AS fA ON fA.user_id = 1
LEFT JOIN friends AS fB ON fB.friend_id = 1
WHERE user.user_id != 1
AND NOT (user.user_id <=> fB.user_id)
AND NOT (user.user_id <=> fA.friend_id)
source to share
You are missing a where clause in your subquery to restrict it to the current friends you are excluding.
$sql = "select * from user u
where u.user_id not in
(select f.friend_id from friends f where f.user_id = ".$_SESSION['id'].")
and u.user_id <> ".$_SESSION['id']." ORDER BY RAND() LIMIT 5";
I would also like to recommend not using string concatenation for this. PDO will be more secure, you will not have the risk of SQL injection.
source to share