MYSQL Left Join last row of result

I am in need of some MYSQL help.

I have a table called Tickets and a table called statusLogs how can I:

select 
  t.*,
  sl.statusId
from
  ticket LEFT JOIN (
    select * from statusLog where ticket_ticketId = t.ticketId order by statusLogId DESC LIMIT 1
  ) sl

Basically, I would like to select the last statusLog for a given ticket number in one statement.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

this is untested, but is one of my ways to do it:

SELECT t.*, sl1.statusId
FROM ticket AS t
LEFT JOIN statusLog AS sL1 ON t.ticketId = sL1.ticketId 
LEFT JOIN statusLog AS sL2 ON t.ticketId = sL2.ticketId AND sL1.statusLogId < sL2.statusLogId 
WHERE sL2.statusLogId IS NULL

Method 2

Try this. It joins a subselect of statusLog which pulls the highest (therefore most recent I think) statusLogId for each ticket_ticketId. That retrieves the statusLogId for the ticket. A further join then matches the statusId to the statusLogId located in the first join.

SELECT
  t.*,
  slid.statusId
FROM
  ticket t LEFT JOIN (
    SELECT ticket_ticketId, MAX(statusLogId) AS statusLogId FROM statusLog GROUP BY ticket_ticketId
  ) sl ON t.ticketId = sl.ticket_ticketId
  JOIN statusLog slid ON slid.statusLogId = sl.statusLogId

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0