SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Addict ketting00's Avatar
    Join Date
    Jul 2011
    Posts
    325
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    What is the best way to join these 3 tables

    Hi, I have these tables

    Code:
    Table Relationship
    ID        ID1          ID2         Relationship
    1          1            2            friend
    2          3            4            family 
    
    Table User
    Id        Name       
    1         John
    2         Jim
    
    Table Photo
    Photo_ID      User_id       Path                 Avatar
    1                 1                path_to_photo   0
    2                 1                path_to_photo   0     
    3                 1                path_to_photo   0
    4                 1                path_to_photo   1
    5                 2                path_to_photo   1
    6                 2                path_to_photo   0
    7                 1                path_to_photo   0
    This is my codes and it's not effective
    Code:
    SELECT Relationship.ID, Relationship.ID1, Relationship.ID2, Relationship.Relationship, COUNT (Relationship.Relationship) as friend, User.ID, User.Name, Photo.User_id, Photo.Path, Photo.Avatar
    FROM Relationship
    LEFT JOIN User ON (Relationship.ID1 = User.ID OR Relationship.ID2 = User.ID)
    LEFT JOIN Photo ON (Relationship.ID1 = Photo.User_id OR Relationship.ID2 = Photo.User_id)
    WHERE (Relationship.ID1 = '$user_id' OR Relationship.ID2 = '$user_id') AND Relationship = 'friend'
    ORDER BY Relationship.ID
    LIMIT 5
    My PHP code:
    Code:
    if($row['friend'] >= 1) {
         if($row['Avatar'] == '1') {
              show this picture
              break;
         } else{
              show default picture
         }
    }else{
         show default picture
    }
    I put count result is the most important thing, because it would count unchecked activities between the two people. The code above generate multiple counts. In a real life practice, if I join 2 tables the count is 3 and if join 3 tables it become 12.

    Thanks

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    First of all, you are missing the GROUP BY clause in your query.
    Second, I would use an INNER JOIN between the users table and the relationships table (there should never be userid's in the relationship table that don't exist in the users table).
    And once you use an INNER JOIN between the users table and the relationships table, the join with the photos table should be on the user id from the users table (more logical, and also gets rid of the OR in that join).

    About the wrong count result:
    You are multiplying the count result because each user has more than 1 photo. So you could extract only the avatar (presuming each user has max 1 avatar):
    Code:
    SELECT 
        Relationship.ID
      , Relationship.ID1
      , Relationship.ID2
      , Relationship.Relationship
      , COUNT (Relationship.Relationship) as friend
      , User.ID as UserID
      , User.Name
      , Photo.Path
      , Photo.Avatar
    FROM Relationship
    INNER JOIN User 
    ON (Relationship.ID1 = User.ID OR Relationship.ID2 = User.ID)
    LEFT JOIN Photo 
    ON User.ID = Photo.User_id 
    AND Photo.Avatar = 1
    WHERE (Relationship.ID1 = '$user_id' OR Relationship.ID2 = '$user_id') 
    AND   Relationship = 'friend'
    GROUP BY Relationship.ID
    ORDER BY Relationship.ID
    LIMIT 5
    Or if you want all the photo's, you'll have to count the number of relations in a subquery, and then join the photo table:
    Code:
    SELECT 
        rels.ID
      , rels.ID1
      , rels.ID2
      , rels.Relationship
      , rels.friend
      , rels.UserID
      , User.Name
      , Photo.Path
      , Photo.Avatar
    FROM 
      (SELECT 
           Relationship.ID
         , Relationship.ID1
         , Relationship.ID2
         , Relationship.Relationship
         , COUNT (Relationship.Relationship) as friend
         , User.ID as UserID
         , User.Name
       FROM Relationship
       INNER JOIN User 
       ON (Relationship.ID1 = User.ID OR Relationship.ID2 = User.ID)
       WHERE (Relationship.ID1 = '$user_id' OR Relationship.ID2 = '$user_id') 
       AND   Relationship = 'friend'
       GROUP BY Relationship.ID
      ) as rels
    LEFT JOIN Photo 
    ON User.ID = Photo.User_id 
    ORDER BY Relationship.ID
    LIMIT 5

  3. #3
    SitePoint Addict ketting00's Avatar
    Join Date
    Jul 2011
    Posts
    325
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks, this is better solution than I need basically.


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
  •