SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    May 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Friend of Friend

    Hey Guys,

    Im a newbie on SQL and I got a problem at the following thing:

    I want to create a relationship between a member and the friend of his friend.


    The first level (friend of member) is no problem:
    Code MySQL:
    SELECT m2.name
    FROM member m1
    INNER JOIN friends f ON friend1 = m1.id OR friend2 = m1.id
    INNER JOIN member m2 ON friend1 = m2.id OR friend2 = m2.id
    WHERE m1.name LIKE '%STEVE%'
    AND m1.id != m2.id

    The second level works, too but I do not know how to get only one table and no name double:
    Code MySQL:
    SELECT m2.name, m3.name
    FROM member m1
    INNER JOIN friends f1 ON friend1 = m1.id OR friend2 = m1.id
    INNER JOIN member m2 ON friend1 = m2.id OR friend2 = m2.id
    INNER JOIN friends f2 ON f2.friend1 = m2.id OR f2.friend2 = m2.id
    INNER JOIN member m3 ON f2.friend1 = m3.id OR f2.friend2 = m3.id
    WHERE m1.name LIKE "%Steve%"
    AND m1.id != m2.id AND m2.id != m3.id

    Anyone can help me with my problem?

    Edit: Okay I got a solution but is there a better one?
    Code MySQL:
    SELECT b1.name, b2.name
    FROM member b1
    INNER JOIN friends f ON friend1 = b1.id OR friend2 = b1.id
    INNER JOIN member b2 ON friend1 = b2.id OR friend2 = b2.id
    WHERE b1.name LIKE '%Steve%'
    AND b1.id != b2.id
    UNION
    SELECT b1.name, b3.name
    FROM member b1
    INNER JOIN friends f ON f.friend1 = b1.id OR f.friend2 = b1.id
    INNER JOIN member b2 ON f.friend1 = b2.id OR f.friend2 = b2.id
    INNER JOIN friends f2 ON f2.friend1 = b2.id OR f2.friend2 = b2.id
    INNER JOIN member b3 ON f2.friend1 = b3.id OR f2.friend2 = b3.id
    WHERE b1.name LIKE '%Steve%'
    AND b1.id != b2.id AND b2.id != b3.id

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    That second solution looks okay to me (the one with the Union), but I wouldn't use WHERE b1.name LIKE '%Steve%' but use the ID of the user instead (in Steve's case that's 1). Why? Suppose there also is a user called "Steven", then your query will return all friends of Steve, all friends of friends Steve, all friends of Steven and all friends of friends of Steven. Clearly the friends and friends of friends of Steven have no business in the result set
    Rmon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Member
    Join Date
    May 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey Scallio,

    Thanks for your answer. Of course, ID-is the better solution.


Bookmarks

Posting Permissions

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