Filter out with Join

I have this query:

SELECT DISTINCT(b.friend_id) AS p开发者_如何学Goossible_id
FROM friends_friends a
    JOIN friends_friends b 
      ON b.user_id = a.friend_id
WHERE a.user_id = 123456789

Essentially what it does is it get a list of possible friends by getting the users friends friends

Now what I want to do is filter out the list of that particular users friends

Here is an example to get that users friends

SELECT friend_id
FROM friends_friends
WHERE user_id = 123456789

I want to achieve this using a Join, I know its simple but I can't seem to wrap my head around it.

you can add a NOT IN clause to your where clause:

and b.friend_id NOT IN (SELECT friend_id
FROM friends_friends
WHERE user_id = 123456789)

SELECT DISTINCT(b.friend_id) AS possible_id
FROM friends_friends a
     JOIN friends_friends b 
       ON b.user_id = a.friend_id
LEFT JOIN friends_friends n
       ON b.user_id = n.friend_id AND n.user_id = 123456789 
WHERE a.user_id = 123456789 AND n.friend_id IS NULL

I think I got that right...

SELECT DISTINCT(b.friend_id) AS possible_id
FROM friends_friends a
    JOIN friends_friends b 
      ON b.user_id = a.friend_id
    LEFT JOIN friends_friends c
      ON c.user_id = a.user_id
     AND b.family_id = c.friend_id
WHERE a.user_id = 123456789
AND c.friend_id IS NULL

This worked for me :)