Many to many relation filter

Question

I have the following tables:

user (id, firstname, lastname)

follow (id, follower_id, following_id)

Now imagine we have users with id 1, 2, 3, 4, 5

And user_id = 1 already following user 2 and 3.

Now I want to write a query that gives me the user_id's that I (user_id = 1) am not following which are (4 and 5).

Can someone please help.

Answer 1

This should do it:

SELECT id FROM user 
WHERE 
    id NOT IN 
    (
        SELECT following_id 
        WHERE follower_id = 1 --(or you can use any user i, i used 1 to show an example)
    )

Answer 2

SELECT * FROM user_table
LEFT JOIN follow_table ON user_table.id = follow_table.following_id
WHERE follow_table.following_id IS NULL

Answer 3

Try this:

SELECT * FROM user_table ut
LEFT JOIN follow_table ft ON ut.id = ft.following_id AND ft.follower_id = 1
WHERE ft.following_id IS NULL