How to list all pending friend requests for a given user ID?

I have a friendship table like this

A and B are friends if there are (A,B) and (B,A) rows. For example, uid 132 & 67 are friends indicated by (132,67) on 2nd row and (67,132) on ninth row. Rows 4 - 8 are pending requests to uid 132.

My question is how to list all pending requests to uid X? I tried this (X=132):

``````SELECT a.uid1, a.uid2 FROM tbl_friendship a LEFT JOIN tbl_friendship b ON a.uid1=b.uid2 AND a.uid2=b.uid2 WHERE a.uid1 IS NULL AND b.uid2 =132
``````

Still not right because the result is empty. The expected result is something like:

``````83, 90, 93, 94, 97
``````

How to fix the query?

how to list all pending requests to uid X? I tried this (X=132)

``````SELECT GREATEST(uid1, uid2), LEAST(uid1, uid2)
FROM tbl_friendship
WHERE 132 IN (uid1, uid2)
GROUP BY 1, 2
HAVING COUNT(*) = 1
``````

I'm only interested to find everyone who send friend request to 132. – anta40

``````SELECT t1.uid2
FROM tbl_friendship t1
WHERE t1.uid2 = 132
AND NOT EXISTS ( SELECT NULL
FROM tbl_friendship t2
WHERE (t1.uid1, t1.uid2) = (t2.uid2, t2.uid1) );
``````
• I don't quite understand the `GROUP BY`, these are the aliases of `GREATEST`, `LEAST`? Apr 18, 2020 at 7:19
• @danblack Columns selected for output can be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or column positions. Column positions are integers and begin with 1. MySQL 8.0 Reference Manual / ... / SELECT Statement. Apr 18, 2020 at 8:54

With a self join on `friendship` and having one table as the `friendship_request` and the other as the `existing`. So search the `friend_requests` where there is no `existing_friend` (the `IS NULL` criteria).

``````SELECT friend_request.uid1 as requested_friendship
FROM friendship as friend_request
LEFT JOIN friendship as existing_friend
ON friend_request.uid1 = existing_friend.uid2
AND existing_friend.uid1 = X
WHERE friend_request.uid2=X
AND existing_friend.uid1 IS NULL
``````

Note: this assumes the friendship relationship is unidirectional

• Hmm so what are the structures of the `friendship_request` and `friendship` tables? Apr 18, 2020 at 18:51
• `friendship_request` is an alias (the `FROM ` clause) of the `friendship` table you put in the question. Apr 18, 2020 at 23:39