Query to show last correspondence on new/open tickets

[crythias]

This query will show last visible to customer entries for all new and open tickets:

Code: Select all

SELECT "ticketnumber", "title", "service", "owner", "customer", "from", "to", "subject", "body", "when" UNION
SELECT t.tn tn, t.title title, s.name as service, u.login owner, t.customer_user_id customer, a.a_from "from", a.a_to "to", a.a_subject subject, a.a_body body, a.create_time "when"
FROM `ticket` t left join service s on s.id=t.service_id left join users u on u.id = t.user_id left join (
select max(a1.id), a1.ticket_id, a1.a_body, a1.a_subject, a1.a_from, a1.a_to, a1.create_time from article a1 left join ticket t on t.id=a1.ticket_id where a1.article_type_id in (1,8,10) group by a1.ticket_id) a on a.ticket_id = t.id
where t.ticket_state_id in (1,4)