CODEWITHSUNDEEP
mysql
Manx_Warrior
Manx_Warrior

Reputation: 99

Mysql query to find "Not a friend"

I want a mysql query which retrieve records list of users who are not friends of current user and friend request sent by current user to other users.

Here is how my users table (id, username) Here is my friends table (id, friend_one, friend_two, status)

status 0 indicates pending request and 1 indicates request accepted. When request is accepted 1 update and insert query is fired. For example (1,4,1,0) 4 sends request to 1 and user 1 accepts the request now the entry in friends table will look like (1,4,1,1) (2,1,4,1).

users(1,'A')

users(2,'B')

users(3,'C')

users(4,'D')

users(5,'E')

Friends table entry

friends (1,4,1,1)

friends (2,1,4,1)

friends (3,1,2,0)

friends (4,3,1,0)

Expected Result

username id status

B 2 0

E 5 NULL

EDIT

This is what I am trying but I am also getting a record who have sent me a friend request which I dont want 

SELECT u2.username, u2.id, f.status
FROM users u1
INNER JOIN users u2
ON u1.id != u2.id
LEFT OUTER JOIN friends f
ON u1.id = f.friend_one
AND u2.id = f.friend_two
WHERE u1.id = 1
AND (f.status IS NULL
OR f.status = 0)

Upvotes: 0

Views: 244

Answers (1)

drizkol
drizkol

Reputation: 155

I think I understand where you're going with this.. this answer should get you close. 

select users.name, users.id, coalesce(friends.status, friends2.status) as status
from users
left join friends on friends.friend_two = 1 and friends.friend_one = users.id
left join friends friends2 on friends2.friend_two = users.id and friends2.friend_one = 1
where users.id != 1
and (friends.status is null or friends.status != 1)
and (friends2.status is null or friends2.status != 1)

Upvotes: 1

Related Questions