What is the best way to join these 3 tables

Databases
1

Hi, I have these tables


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


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:


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

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):


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:


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

Thanks, this is better solution than I need basically.