MYSQL Left Join last row of result

Question

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.

Answer 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

Answer 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