SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trying to "JOIN" 2 tables?!?!

    I'm trying to make this work:
    PHP Code:
    $sql "SELECT
                u.new_userid
              , u.fname
              , u.lname
              , u.email
              , u.img_small
              , u.seouname
            FROM fb_users u
            INNER JOIN fb_user_friends uf
            ON uf.user = u.new_userid
            WHERE u.new_userid <> 
    $profileUserID
            UNION
            SELECT
                u.new_userid
              , u.fname
              , u.lname
              , u.email
              , u.img_small
              , u.seouname
            FROM fb_users u
            INNER JOIN fb_club_users cu
            ON cu.new_userid = u.new_userid
            INNER JOIN
              (SELECT
                   clubid
               FROM fb_club_users
               WHERE new_userid = 
    $profileUserID
              ) AS c
            ON c.clubid = cu.clubid
            WHERE u.new_userid <> 
    $profileUserID"
    It allmost works like a charm but... In this part:
    Code:
    INNER JOIN fb_user_friends uf
    ON uf.user = u.new_userid
    WHERE u.new_userid <> $profileUserID
    ... it goes wrong.

    The table fb_user_friends looks like this:
    id, user, friend

    Now I only want to get friend out of the table where the user i the current "$profileUserID", but as it is now, it pulls all the "friend" records... Where do I go wrong?

    Thanks in advance :-)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    let's start here --
    Code:
      FROM fb_users u
    ...
     WHERE u.new_userid <> $profileUserID
    this says "go get ~all~ users except for this one"

    and then for each user, you do this --
    Code:
    INNER 
      JOIN fb_user_friends uf         
        ON uf.user = u.new_userid
    which basically says "get all friends of all users (except for that one)"

    could you perhaps explain in words what you actually wanted?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    let's start here --
    Code:
      FROM fb_users u
    ...
     WHERE u.new_userid <> $profileUserID
    this says "go get ~all~ users except for this one"
    Well, this gets all the users of a group the current user is a member of except the current user... This is working just fine ;-)

    Quote Originally Posted by r937 View Post
    and then for each user, you do this --
    Code:
    INNER 
      JOIN fb_user_friends uf         
        ON uf.user = u.new_userid
    which basically says "get all friends of all users (except for that one)"

    could you perhaps explain in words what you actually wanted?

    This part is not working correct... I want to get all friends from this table where the "user" is the current user...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that's the part that doesn't make sense

    by the way, i'm looking only at the first SELECT in the UNION, the group stuff will have to come later

    let's consider some sample data

    users
    tom
    dick
    harry
    curly
    larry
    moe

    friends
    tom dick
    tom harry
    curly larry
    moe larry

    let's say that $profileUserID equals curly

    so the first part of the join (in the first SELECT), where you want user <> 'curly', that will retrieve

    tom
    dick
    harry
    larry
    moe

    then for ~each~ of these, you are joining to the friends table, and the result of this join is

    tom dick
    tom harry
    moe larry

    note that dick and larry were eliminated from the inner join, because each has no friends


    did you follow that?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    note that dick and larry were eliminated from the inner join, because each has no friends

    did you follow that?
    So, I have to get users friends first like this before the UNION:
    PHP Code:
    SELECT friend FROM fb_user_friends WHERE user $profileUserID 
    But that would give a problem with different number of columns?!?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jmansa View Post
    But that would give a problem with different number of columns?!?
    maybe we could step back just a tiny bit and start over

    what is the entire ~first~ query supposed to do?

    in words, please

    also, are you storing two rows for each relationship, or is each relationship uni-directional?

    in other words, if humpty is friends with dumpty, how do you decide whether humpty goes into the user column and dumpty in the friend column, or vice versa? or both?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what is the entire ~first~ query supposed to do?

    in words, please
    The first query, before UNION, is supposed to get all the current user friends from the table fb_user_friends where the current user is listed as "user".

    are you storing two rows for each relationship, or is each relationship uni-directional?
    As it is now, I'm storing two records for each relationship, but I guess I could just use one!? It is on a trial basis so it could be changed with no fuss if it helps the process...

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jmansa View Post
    The first query, before UNION, is supposed to get all the current user friends from the table fb_user_friends where the current user is listed as "user".
    here ya go...
    Code:
    SELECT u.new_userid
         , u.fname
         , u.lname
         , u.email
         , u.img_small
         , u.seouname         
      FROM fb_user_friends AS uf
    INNER
      JOIN fb_users AS u
        ON u.new_userid = uf.friend
     WHERE uf.user = $profileUserID
    somewhat different from your first query, yes?

    can you do the second query?



    Quote Originally Posted by jmansa View Post
    As it is now, I'm storing two records for each relationship
    that's fine, that's actually more efficient for the queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    here ya go...
    Code:
    SELECT u.new_userid
         , u.fname
         , u.lname
         , u.email
         , u.img_small
         , u.seouname         
      FROM fb_user_friends AS uf
    INNER
      JOIN fb_users AS u
        ON u.new_userid = uf.friend
     WHERE uf.user = $profileUserID
    somewhat different from your first query, yes?

    can you do the second query?



    that's fine, that's actually more efficient for the queries
    Thanks my man... That did the trick. You are the best :-)


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
  •