CODEWITHSUNDEEP
mysqlsql
Steven
Steven

Reputation: 401

SQL getting max id field on a LEFT JOIN

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

Upvotes: 10

Views: 38618

Answers (1)

Terje D.
Terje D.

Reputation: 6315

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.

Upvotes: 34

Related Questions