Help Writing "Friend Requests" Query

pws5068 · #1 (**permalink**) 06-19-09, 09:29

Greetings All, I'm relatively new to the database world and I'm stuck on a pretty basic problem.. perhaps someone here can help

I have a simple Friends Table set up as follows:
[ AccountID | FriendID ]

Where a friend connection between Person1 and Person 2 would have two rows: (1,2) and (2,1). Thus if 3 has a pending request for 1 the row (3,1) exists but Not it's reciprocal (1,3).

I wrote a statement to see if PersonA and PersonB are Friends..:

SELECT * FROM Friends WHERE AccountID = 'PersonA' AND FriendID='PersonB'
UNION
SELECT * FROM Friends WHERE AccountID = ' PersonB' AND FriendID='PersonA';

Now I'm really stuck... how would I write a query to find only Pending Friend Requests for PersonA?

r937 · #2 (**permalink**) 06-19-09, 09:37

this query returns friends that PersonA has requested --

Code:

SELECT request_from.FriendID 
  FROM Friends AS request_from
LEFT OUTER
  JOIN Friends AS request_to
    ON request_to.AccountID = request_from.FriendID
 WHERE request_from.AccountID = 'PersonA'
   AND request_to.AccountID IS NULL

pws5068 · #3 (**permalink**) 06-19-09, 10:07

Hmmm.. I set up a couple rows to test the query,

[ AccountID | FriendID ]
____1_____|___ 2__
____2_____|___1__
____3_____|___1__

So applying your query to AccountID 1:

Code:

SELECT request_from.FriendID 
  FROM Friends AS request_from
LEFT OUTER JOIN Friends AS request_to
    ON request_to.AccountID = request_from.FriendID
 WHERE request_from.AccountID = '1' AND request_to.AccountID IS NULL

Returns an empty set instead of the expected value 3. (I also tested with the values 2 and 3 which return an empty set as expected.)

r937 · #4 (**permalink**) 06-19-09, 11:10

Quote:

Originally Posted by pws5068

Returns an empty set instead of the expected value 3.

no, this is wrong

if you feed 1 into the query, you are asking for all the friends that 1 has requested (1,x) where there is no corresponding (x,1), correct?

in the sample data just provided, that would correctly be an empty set

i had the query lightly wrong, try this --

Code:

SELECT request_from.FriendID 
  FROM Friends AS request_from
LEFT OUTER
  JOIN Friends AS request_to
    ON request_to.AccountID = request_from.FriendID
   AND request_to.FriendID = request_from.AccountID
 WHERE request_from.AccountID = 1
   AND request_to.AccountID IS NULL

this returns empty set for 1 and 2, but for 3 it returns 1

pws5068 · #5 (**permalink**) 06-19-09, 11:26

Thank you, Your query works and I have used it for another part of this project, but forgive me for being unclear.

Quote:

how would I write a query to find only Pending Friend Requests for PersonA?

What I should have asked is how to find which users have requested to be PersonA's friend.. not who PersonA is waiting for. I am attempting to modify your query for this purpose.

pws5068 · #6 (**permalink**) 06-19-09, 11:35

Ah, it was an easier modification than I expected. Essentially I needed the exact opposite of the query you gave me... so simply changing every instance of AccountID to FriendID and vise-versa gave me the results I hoped for.

Code:

SELECT request_from.AccountID
  FROM Friends AS request_from
LEFT OUTER
  JOIN Friends AS request_to
    ON request_to.FriendID = request_from.AccountID
   AND request_to.AccountID = request_from.FriendID
 WHERE request_from.FriendID = 1
   AND request_to.FriendID IS NULL

Now, Querying user 1 reveals that user 3 has requested a Friends connection.

Thanks!!

Internet Services

Web Development

Digital Marketing

Consumer Tech