0

I have a friendship table like this

enter image description here

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?

0

2 Answers 2

2

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) );
2
  • I don't quite understand the GROUP BY, these are the aliases of GREATEST, LEAST?
    – danblack
    Apr 18, 2020 at 7:19
  • 2
    @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.
    – Akina
    Apr 18, 2020 at 8:54
0

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.