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.
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.
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
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