Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2008
    Posts
    53

    Unanswered: table structure - Friends

    I have table Friends:

    UserId | FriendId
    ----------------
    1 | 2
    2 | 1
    3 | 2
    2 | 4

    For example: when user with id = 1 invites user with id = 2, I insert one row in table: UserId = 1, FriendId = 2. They AREN'T friends yet ! User with id = 2 must accept user with id = 1, then I insert second row: UserId = 2, FriendId = 1. Now they are friends.

    But how can I in that case get all friends for example user with id = 2 - he has only one friend - user with id = 1 (user with id = 4 isn't his friend yet) ? Maybe I should modify table or I don't know.
    Last edited by chomik; 03-13-11 at 16:08.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    with a self-join
    Code:
    SELECT friend.userid
      FROM friends AS user
    INNER
      JOIN friends AS friend
        ON friend.userid = user.friendid
       AND friend.friendid = user.userid
     WHERE user.userid = 937
    it's actually quite simple once you see how it works

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2008
    Posts
    53
    thx very much - I have one more question:

    If I want get records which don't have opposite - for example record with UserId = 2 and FriendId = 4 (so we don't have record with UserId = 4 and FriendId = 2) - how can I do that ?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change it to a LEFT OUTER JOIN and check for NULL in the join column, meaning that the reciprocal row is missing

    classic technique

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2008
    Posts
    53
    Oh thx very much for suggestion - so the sql query is:

    Code:
    SELECT *
    FROM friends AS user
    LEFT JOIN friends AS friend ON
    user.userid = friend.friendid AND user.friendid = friend.userid
    WHERE friend.userid IS NULL AND friend.friendid IS NULL
    AND user.userid = 2

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, except why did you flip the two join conditions over? i thought they make more sense the other way around, and i believe my indentation enhances comprehension as well

    but i guess it's a question of style, and i am a stickler after seeing so many thousands of queries

    by the way, you only need one of those IS NULL tests
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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