2

enter image description here

I am trying to understand how to select a user from users table that is not associated with current user in user_follows table: neither in users_id not in shown_users_id.

Basically a new user that not related to my current user.

Tried something like this:

SELECT 
   user.id
FROM
   users AS user
LEFT JOIN
   user_follows AS follow ON user.id = follow.users_id
LEFT JOIN 
   user_follows AS follow2 ON user.id = follow2.shown_users_id
WHERE
   user.id != 2 # (user that I don't wan't to select) 
LIMIT 1;

But it would still return me the row with user 3 in it.

So if in my users table I have user 2,3,4, and with the following data in user_follows table:

enter image description here

I only want to get user 4, since it is not associated with user 2 at this moment.

3

You need 2 anti-joins. Written with either two LEFT JOIN / IS NULL:

SELECT 
   user.id
FROM
   users AS user
LEFT JOIN
   user_follows AS follower ON  follower.users_id = user.id 
                            AND follower.shown_users_id = 2
LEFT JOIN 
   user_follows AS followed ON  followed.shown_users_id = user.id 
                            AND followed.users_id = 2
WHERE
   follower.users_id IS NULL
  AND
   followed.users_id IS NULL 
  AND
   user.id <> 2 ;

or with two NOT EXISTS subqueries, which I find far more readable:

SELECT 
   user.id
FROM
   users AS user
WHERE
   NOT EXISTS 
      ( SELECT * 
        FROM user_follows AS follower
        WHERE follower.users_id = user.id 
          AND follower.shown_users_id = 2
      )
AND
   NOT EXISTS 
      ( SELECT * 
        FROM user_follows AS followed
        WHERE followed.shown_users_id = user.id 
          AND followed.users_id = 2
      ) 
AND
   user.id <> 2 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.