Query to find out following

Question

I have a users table and a friendships table users(id, email, name) friendships(id, user_id, friend_id, confirmed)

id   |  email  |  name
1    | xyz     | abc
2    | yui     | poi
3    | tar     | foo
4    | bar     | baz

In this if user with id 1 sends a friend request to user with id 2 then the row in friendship with look like

id               |  user_id   |  friend_id  | confirmed
1(generated key) |  1         |  2          | false

now when the user accepts the friends request

id               |  user_id   |  friend_id  | confirmed
1(generated key) |  1         |  2          | true
2                |  2         |  1          | true

So what I basically want to do is find out the users who are not friends with the current user, if user is already friends with the user or user has send a friend request then the resulting array of hash should not contain that value.

So in the above database the result array of hash should contain users with id 3 and 4.

So basically in the friendship table user_id is the friend request sender id and the friend id is the receiver id

so wherever the user_id is the current_user it should not have the users corresponding to that friend_id and vise versa where the friend_id is current user it should not show the users with that corresponding user_id

I hope the question is clear enough to understand

Sorry for poor english

Ravinder Reddy Ravinder Reddy · Accepted Answer · 2014-03-26T00:56:17

Try this:

select u.* from users u 
where u.id not in ( select user_id id from friendship 
                    union 
                    select friend_id from friendship );

If you also want current user's record, add an OR clause with current user id.

select u.* from users u 
where u.id = ?
   OR u.id not in ( select user_id id from friendship 
                    union 
                    select friend_id from friendship );

Fill the parameter with current user id and execute.

Query to find out following

1 Answers