There are two tables:
Table name: ticket
+-------+---------+---------+-------------------+
| id | title | closed | param |
+-------+--------------+------------+-----------+
| 1 | test1 | 0 | 1 |
| 2 | test2 | 1 | 1 |
| 3 | test3 | 0 | 1 |
Table name: ticket_event
+-------+---------+---------+-----------------+
| id | ticket_id | event | date |
+-------+--------------+----------+-----------+
| 1 | 1 | OPENED | |
| 2 | 1 | CLOSED | |
| 3 | 2 | OPENED | |
+-------+--------------+----------+-----------+
I need to get the last record of ticket_event for each ticket. Result should be:
test1 CLOSED
test2 OPENED
test3 NULL
I tried this code but I get all records for ticket_event:
SELECT t1.title as t1Title, t2.event as t2Event FROM ticket t1 LEFT JOIN ticket_event t2 ON t1.id = t2.ticket_id
You can join ticket
to a query that returns the last event::
SELECT t.title, e.event
FROM ticket t LEFT JOIN (
SELECT e.* from ticket_event e
WHERE NOT EXISTS (
SELECT 1 FROM ticket_event
WHERE ticket_id = e.ticket_id AND id > e.id
)
) e
ON t.id = e.ticket_id
See the demo.
Results:
| title | event |
| ----- | ------ |
| test1 | CLOSED |
| test2 | OPENED |
| test3 | |