MySQL - NOT IN produce unwanted result

Question

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 to get all users that are available to be added as friends (in this case, the user_id of 1 will have 3 more friends to be added (2, 3, 5). However, by using the following SQL statement below I only get 1 user (4) available to 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 logged in as user 4 which there are no users available to be added. This is a bit tricky to me. Any idea would be very much appreciated.

Thanks

I tried too, but still does not work. I tried `WHERE friends.friend_id <> $_SESSION['id']` or would it be something else? — Tepken Vannkorn, Jan 22 '13 at 17:18
No - `WHERE friends.user_id = $_SESSION['id']` - You want to find people that *this user* is not already friends with, correct? So you want to say `NOT IN` the list of people said user is friends with. — Mike Christensen, Jan 22 '13 at 17:25

bonCodigo · Answer 1 · 2013-01-22T17:53:15.590

2

How about this? The null shows where user 4 shouldn't be adding user 4 as a friend.. Infact you can filter it out by And FID is not null. If you want to select to be added friends a particular user as per your session id then you can spcify that in another condition as well :)

Updated adding another join to make sure the names are shown for friends, not for ther user. As well as there's no longer 4 exists.

SQLFIDDLE DEMO

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 specifi user e.g. 1

SQLFIDDLE DEMO USER 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 |

edited Jan 22 '13 at 17:53

answered Jan 22 '13 at 17:29

bonCodigo

14,268
1
48
91

@tepkenvannkorn try this out and comment please :) – bonCodigo Jan 22 '13 at 17:34
1

I just got finished posting a similar answer. This seems *way* better than mine! >.> – gen_Eric Jan 22 '13 at 17:39
@RocketHazmat glad for the heads-up just noticed I have given the wrong link for the last query. :D – bonCodigo Jan 22 '13 at 17:48
1

Instead of using a subquery on your 2nd example, you can just `JOIN` the `users` table again to get the usernames. http://sqlfiddle.com/#!2/12de1/25 – gen_Eric Jan 22 '13 at 17:55
1

OP can check on the `explain plan` and decide ;) http://sqlfiddle.com/#!2/12de1/33. I just kept the two queries in the original subquery method to keep consistency. – bonCodigo Jan 22 '13 at 18:01

score 1 · Answer 2 · answered Jan 22 '13 at 17:26

What your SQL query is doing is: Get all users where id is not equal to ANY friend_id and not the current user (I assume).

What you want, is to check that no records exist for the pair of friend_id and user_id in the friend 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";

score 1 · Answer 3 · answered Jan 22 '13 at 17:38

Instead of using NOT IN with a subquery, you can try to JOIN the friends table.

I JOINed it twice. Once where the 1 is the user_id and once where 1 is the friend_id. Then I got all users that aren't 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)

DEMO: http://sqlfiddle.com/#!2/1a7ae/30

manuskc · Accepted Answer · 2013-01-23T05:18:39.480

Edit:

How about this: (in else clause use 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

One more way to solve this (but might not be an optimal solution):

Union query below might not be a costly query compared to complete join between two tables if number of friends an user has is less than total number of users in system.

Also don't forget to add index on uid and fid column of friends table.

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;

http://sqlfiddle.com/#!2/12de1/40

The second option works great and simple. Thanks @Manu for this — Tepken Vannkorn, Jan 23 '13 at 04:10

score 0 · Answer 5 · answered Jan 22 '13 at 17:25

You're missing the where clause in your sub query to limit it to the current users friends that you're 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'd also like to recommend not using string concatenation to do this. PDO would be safer, you would have no risk of SQL injection.

http://php.net/manual/en/ref.pdo-mysql.php

score 0 · Answer 6 · answered Jan 22 '13 at 18:04

0

SELECT u1.id u1_id
     , u2.id u2_id
  FROM users u1
  JOIN users u2
    ON u2.id <> u1.id
  LEFT
  JOIN friends f
    ON (f.fid = u1.id AND f.uid = u2.id)
    OR (f.fid = u2.id AND f.uid = u1.id)
 WHERE f.id IS NULL;

answered Jan 22 '13 at 18:04

Strawberry

33,750
13
40
57

MySQL - NOT IN produce unwanted result

6 Answers6

SQLFIDDLE DEMO