Join the same table to get/compare data (for friend list)

[eevan79]

I have USERS table and FRIENDS table. When user send friend request to other user data have been added into FRIENDS table. When other user accept request, data is also added (for his ID).

 

 

There's no problem to get approved (both data) friends, but I want to get pending list.

 

 

This is fields for FRIENDS table:

user_id | friend_id | friends

 

 

For example we have 2 users: one with ID 1, and other with ID 2. So user with ID 1 send friend request to ID 2 following data will be added into FRIENDS table:

1 | 2 | 1

 

 

When other user accept request:

2 | 1 | 1

 

 

So there is record for both users in FRIENDS table. So, how to get data only when there is one record (to get pending requests)?

 

 

This is query to get friend list:

 

SELECT z.friend_id, u.username, u.user_avatar
	  FROM 
                  " . FRIENDS_TABLE . " AS z, 
	  " . FRIENDS_TABLE . " AS m, 
                  " . USERS_TABLE . " AS u
				WHERE z.friend_id= m.user_id 
				AND m.friend_id= {$userID} 
				AND z.user_id = {$userID} 
				AND z.friend_id= u.user_id
				ORDER BY LOWER(u.username) DESC

Now I need query to get pending request (those that the user has not yet approved)?

[/size]

[Muddy_Funster]

Easiest way would be to fix your FRIENDS table to include a pending field.  This would let you set Pending, Accepted, Refused/Ignored as values and filter accordingly.

[kickstart]

Hi

 

Tend to agree with the above.

 

However I would think that you could left outer join friends with itself, where friend id on one is user id on the other AND vice versa. Then only return rows where there is no matching row.

 

Something like this:-

 

SELECT *
FROM friends a
LEFT OUTER JOIN friends b
ON a.user_id = b.friend_id
AND a.friend_id = b.user_id
WHERE b.user_id IS NULL

 

All the best

 

Keith

[eevan79]

I have field "friends" 0/1 for accepted requests.

 

 

@kickstart,

tested and working fine. Thats query I looking for.