Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Posts
    4

    Cool Unanswered: Help Writing "Friend Requests" Query

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2009
    Posts
    4
    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.)
    Last edited by pws5068; 06-19-09 at 11:53.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2009
    Posts
    4
    Thank you, Your query works and I have used it for another part of this project, but forgive me for being unclear.

    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.
    Last edited by pws5068; 06-19-09 at 12:30.

  6. #6
    Join Date
    Jun 2009
    Posts
    4
    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!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •