I am trying to pull the photo from tblimage that corresponds to the maxid in the tblimage for each user. Currently, I am getting all the messages from the message table and a random photo for the user that posted the message, I would like the photo to be the latest uploaded photo. the way its written now it just pulls a random photo from the table. any suggestions?
table structures are as such:
messages: msgid, message, user_id, event_id
tblimage: id, photo, userid
SELECT messages.*, tblimage.photo, max(tblimage.id)
FROM messages LEFT JOIN tblimage ON messages.user_id = tblimage.userid
GROUP BY messages.msg_id, messages.user_id
ORDER BY messages.msg_id DESC, tblimage.id desc
Try
SELECT messages.*, T2.photo
FROM messages
LEFT JOIN (SELECT userid, MAX(id) AS maxid
FROM tblimages
GROUP BY userid) AS T1
ON messages.user_id = T1.userid
LEFT JOIN tblimages AS T2
ON T2.id = T1.maxid
ORDER BY messages.msg_id DESC
which finds max(id) for each user in tblimages, then uses that to join each user to the latest photo for that user.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With