mySql - find all not friends of a user -

i making social network, , i'm trying write mysql query find users not friends of specific user. there 1 table - users,that has id_user,name , email.

users: user_id , name , email.. 1         dan     a@a.com 2         ron     b@b.com 3         james   c@c.com 4         joe     d@d.com 5         max     c@c.com 

and friends table show relationships (status=2 mean 2 friends):

friends: user_id , friend_id , status 1             2         2 3             1         2 4             3         2  

what mysql query gives list of user_id(from users) not friends of user_id =1 ? (users 4 , 5).

thank's lot,

an anti-join pattern should sufficient.

it looks "a isa friend of b" can represented in friends table a row, either (a,b,2) or (b,a,2). (that's not entirely clear me, i'm going assume that's true now.)

this query first cut. check friends both ways in friends table, i'm going use or condition in join predicate.

this getting rows users, along "matching" rows friends. "trick" predicate in where clause, eliminates rows found "match" in friends, leaving rows users didn't have "match".

select u.user_id      , u.name   users u   left   join friends f     on ( f.user_id   = u.user_id , f.friend_id = ? , f.status = 2 )     or ( f.friend_id = u.user_id , f.user_id   = ? , f.status = 2 )  f.user_id   null     or f.friend_id null 

unfortunately, mysql optimizer doesn't handle these or conditions elegantly. cut @ re-write, can try splitting join friends 2 left joins.

i think equivalent:

select u.user_id      , u.name   users u   left   join friends f     on ( f.user_id   = u.user_id , f.friend_id = ? , f.status = 2 )   left   join friends g     or ( g.friend_id = u.user_id , g.user_id   = ? , g.status = 2 )  f.user_id   null     , g.friend_id null 

(i haven't tested second query... goal same thing. rows users, along matching rows friends, , filter out rows had match.

edit

i think we'd need filter out row users "of specific user". queries above return user himself "not friend" of himself. accomplish that, add where clause

 , u.user_id <> ? 

for first query, need take care order of precedence between or , and boolean operators... think we'd need wrap 2 or'd conditions in first query in parens.

---

followup

there couple of other query patterns can return equivalent result. 2 examples, can use predicates of form not in (subquery), or predicates of form not exists (subquery).

using "not in (subquery)"

with form, take care subquery not return null value. if there's null value in list, predicate not return true.

select u.user_id      , u.name   users u  u.user_id not in ( select f.user_id                             friends f                            f.user_id not null                              , f.status = 2                              , f.friend_id = ?                         )    , u.user_id not in ( select f.friend_id                             friends f                            f.friend_id not null                              , f.status = 2                              , f.user_id = ?                         )    , u.user_id <> ? 

using "not exists (subquery)"

select u.user_id      , u.name   users u  not exists ( select 1                        friends f                      f.user_id = u.user_id                        , f.status = 2                        , f.friend_id = ?                   )    , not exists ( select 1                       friends g                      g.friend_id = u.user_id                        , g.status = 2                        , g.user_id = ?                   )    , u.user_id <> ?