Paul Paul - 9 days ago 7
MySQL Question

JOIN query not producing the correct results

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

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