Geek asks and answers

SQL gets maximum id field in LEFT JOIN

I am trying to pull a photo from tblimage that matches maxid in tblimage for each user. Currently, I receive all messages from the message table and a random photo for the user who posted the message, I would like the photo to be the last photo uploaded. the way it is written, now he just pulls a random photo from the table. any suggestions?

table structures are as follows:

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
+5
source share
1 answer

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

max (id) tblimages, .

+21

More articles:


All Articles