Reputation: 99
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
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