Left Join Order By

luiddog

I have the a user table and a user's photo table. Each user has about 6 photos. I'm trying to set the user's main photo as the most recently added photo. On the page where the user's info and main photo is displayed I'm using:

SELECT
*
FROM users 
LEFT JOIN users_photos 
ON users.uid = users_photos.upuid
WHERE users.featured =  'yes' 
AND users.type =  'Member'
GROUP BY
users_photos.upuid
ORDER BY users_photos.upTime DESC

However, the result doesn't give the most recently added photo. It results in the photo that isn't the oldest or newest but rather the middle photo.

I tried a number of different GROUP BY and ORDER BY but nothing.

reddrum

Hey luiddog,
What happens if you drop the "GROUP BY..." and just use the "ORDER BY..."?

luiddog

Then the LEFT JOIN results in all of the photos. So it would be:

User1 - Photo1
User1 - Photo2
User1 - Photo3
User1 - Photo4
User1 - Photo5
User1 - Photo6
User2 - Photo1
User2 - Photo2
User2 - Photo3
etc...

reddrum

With that in mind maybe you could do somthing like this:

$user = array();
$photo = array();
$temp = $user_from_query[0];
$i2 = 0;
foreach($user_from_query as $u){
   if($u != $temp){
      $user[$i2] = $temp;
      $photo[$i2] = $photo_from_query[($i - 1)];
      $temp = $u;
      ++$i2;
   }
    ++$i;
}

Lars_Berg

SELECT *
  FROM users as u
LEFT JOIN users_photos up
ON u.uid = up.upuid
and up.upTime in 
  (select max(upTime)
     from users_photos ups
    where ups.upuid = up.upuid)
WHERE u.featured = 'yes'
AND u.type = 'Member'