All geek questions in one place

MYSQL Left Join the last line of the result.

I need 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 a single expression.

+6
source share
2 answers

Try it. It joins the statusLog statusLog , which pulls the highest (hence the most recent, I think) statusLogId for each ticket_ticketId . This retrieves the statusLogId for the ticket. A further connection then matches statusId with statusLogId located in the first connection.

 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
+2
source

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

 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
+21
source

Source: https://habr.com/ru/post/897683/

More articles:


All Articles