JOIN query not producing the correct results

Question

I have the following query where I am trying to join my profile_img and users table to match the id's in the friends table (friend_one or friend_two) in oder to get their profile image or user information.

As of now, I do not get any errors...just not the correct results I am looking for. There should be two results that show relation to :profile_user... 5 and 2, which would also give ocean and lake for their profile_img.

The parameter :profile_user is equal to 1. :total_status = 2.

I am not sure if my ON clauses are throwing this off or not. I am not sure how to make u.id = to both the friend_one or friend_two.

Does anyone see why this isn't working?

Here is a fiddle

SELECT f.*, u.*, p.*, IFNULL(p.img, 'profile_images/default.jpg') AS img
        FROM friends f
        JOIN
            users u
            ON u.id = (f.friend_one or f.friend_two)
        LEFT JOIN
            profile_img p
            ON p.user_id = f.friend_one or f.friend_two and p.id = (select max(p2.id) from profile_img p2 where p2.user_id = p.user_id)     
        WHERE (friend_one = :profile_user or friend_two = :profile_user)
        AND status = :total_status

Full code, which is showing 0 results.

$friend_status = 2;
    $friend_sql = "
        SELECT f.*, u.*, p.*, IFNULL(p.img, 'profile_images/default.jpg') AS img
        FROM friends f
        JOIN
            users u
            ON u.id = (f.friend_one or f.friend_two)
        LEFT JOIN
            profile_img p
            ON p.user_id = f.friend_one or f.friend_two and p.id = (select max(p2.id) from profile_img p2 where p2.user_id = p.user_id)     
        WHERE (friend_one = :profile_user or friend_two = :profile_user)
        AND status = :total_status
    ";
    $friend_stmt = $con->prepare($friend_sql);
    $friend_stmt->execute(array(':profile_user' => $profile_user, ':total_status' => $friend_status));
    $friend_total_rows = $friend_stmt->fetchAll(PDO::FETCH_ASSOC);
    $count_total_friend = $friend_stmt->rowCount();
?>  
        <div id="friend-list-container">
            <div id="friend-list-count">Friends <span class="light-gray"><?php echo $count_total_friend; ?></span></div>
            <div id="friend-list-image-container">
<?php
    foreach ($friend_total_rows as $friend_total_row) {
        $friend_1           = $friend_total_row['friend_one'];
        $friend_2           = $friend_total_row['friend_two'];
        $friend_img         = $friend_total_row['img'];
        $friend_username    = $friend_total_row['username'];
        if($friend_1 !== $profile_user) {
            echo $friend_1;
            echo $friend_img;
            echo $friend_username;
        }
        if($friend_2 !== $profile_user) {
            echo $friend_2;
            echo $friend_img;
            echo $friend_username;
        }
    }

Answer 1

after I posted the below I realized mysql does not support cte -- here a version without:

SELECT f.*,
       u1.*,
       u2.*,
       p1.*,
       p2.*,
       IFNULL(p1.img, 'profile_images/default.jpg') AS img1,
       IFNULL(p2.img, 'profile_images/default.jpg') AS img2
FROM friends f
LEFT JOIN users u1 ON u1.id = f.friend_one 
LEFT JOIN users u2 ON u2.id = f.friend_two
LEFT JOIN (
  SELECT user_id, max(id) as mid
  FROM profile_img
  GROUP BY user_id
) max1 ON u1.user_id = max1.user_id
LEFT JOIN (
  SELECT user_id, max(id) as mid
  FROM profile_img
  GROUP BY user_id
) max2 ON u2.user_id = max2.user_id
LEFT JOIN profile_img p1 ON p1.user_id = f.friend_one and p1.id = max1.mid
LEFT JOIN profile_img p2 ON p2.user_id = f.friend_two and p2.id = max2.mid
WHERE (friend_one = :profile_user or friend_two = :profile_user)
        AND status = :total_status

---

WITH maxImage AS
(
  SELECT user_id, max(id) as mid
  FROM profile_img
  GROUP BY user_id
)
SELECT f.*,
       u1.*,
       u2.*,
       p1.*,
       p2.*,
       IFNULL(p1.img, 'profile_images/default.jpg') AS img1,
       IFNULL(p2.img, 'profile_images/default.jpg') AS img2
FROM friends f
LEFT JOIN users u1 ON u1.id = f.friend_one 
LEFT JOIN users u2 ON u2.id = f.friend_two
LEFT JOIN maxImage max1 ON u1.user_id = max1.user_id
LEFT JOIN maxImage max2 ON u2.user_id = max2.user_id
LEFT JOIN profile_img p1 ON p1.user_id = f.friend_one and p1.id = max1.mid
LEFT JOIN profile_img p2 ON p2.user_id = f.friend_two and p2.id = max2.mid
WHERE (friend_one = :profile_user or friend_two = :profile_user)
        AND status = :total_status

Answer 2

Based on the data in the mentioned SQL Fiddle. Below is the query that i think will help

select 
res1.firstname as FriendOneFirstName,
res1.lastname as FriendOneLastName,
res1.img as FriendOneImage,
user1.firstname as FriendTwoFirstName,
user1.lastname as FriendTwoLastName,
pf1.img as FirendTwoProfileImage
from
(select usr.id,usr.firstname,usr.lastname,pf.img,frds.friend_two 
 from users usr 
 inner join friends frds on usr.id=frds.friend_one
 inner join profile_img pf on usr.id = pf.user_id
 ) as res1
inner join users user1 on user1.id=res1.friend_two
inner join profile_img pf1 on user1.id=pf1.user_id
order by user1.id;

Answer 3

If you have an "id" column in your friend table that is the same as the column "id" of your user table, i think you should try this

ON u.id = f.id

instead of

ON u.id = (f.friend_one or f.friend_two)