Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL getting max id field on a LEFT JOIN

Tags:

sql

mysql

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
like image 649
Steven Avatar asked Feb 03 '13 22:02

Steven


1 Answers

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.

like image 107
Terje D. Avatar answered Sep 18 '22 13:09

Terje D.