Question:

I want to create a report that includes the first message on a ticket. I've tried using 'SELECT tickets_messages.message', but that doesn't work. How can I include message data in my report?

Answer:

The issue here is that you can only reference one table in your query's FROM clause. So if you are selecting fields from the 'tickets' table (e.g. your SELECT statement includes 'tickets.id', 'tickets.subject' etc.), you can't also select from 'tickets_messages'.

Instead, you have to use table cross-referencing to get message content using:

SELECT tickets.messages.message

One ticket can have many messages, so doing this will return multiple rows for each ticket that has more than message. To fix this, you can use 'GROUP BY tickets.id' so that only one row is displayed per ticket (containing the first message on the ticket).

Here's an example query that combines 'tickets' and 'tickets_messages'  content:

SELECT tickets.date_created, tickets.subject,  tickets.messages.message, tickets.department

FROM ticketsWHERE tickets.date_created = %LAST_WEEK%

SPLIT BY tickets.department

GROUP BY tickets.id