In SQL, you can combine rows from more than one table using JOINs. These aren’t available in DPQL, but there is a way to combine data from multiple tables.

For example, suppose you want a report that mainly contains information about ticket messages, but also includes information about departments.

Remember, the FROM clause of your query can only include one table:

FROM tickets_messages
 copy

Looking at the  Tickets you can see that there is no department field in that table.

You can’t put SELECT tickets_messages.department in your query. That won’t work.

However, tickets_messages does have a ticket field which is the Ticket data type. This is italicised to show that it is used to cross-reference to another table, and will not return data directly.

This means you can use tickets_messages.ticket to cross reference and retrieve fields from the tickets table, even though you’re using tickets_messages in the FROM clause.

So to get the department of the ticket, you can do the following in your SELECT clause:

SELECT tickets_messages.ticket.department
 copy

Not every table is available for cross-referencing. You can use cross-referencing with these tables:

• articles

• article_attachments

• article_comments

• chat_conversations

• chat_messages

• downloads

• download_comments

• feedback

• feedback_attachments

• feedback_comments

• labels_articles

• labels_chat_conversations

• labels_downloads

• labels_feedback

• labels_news

• labels_organizations

• labels_people

• labels_tasks

• labels_tickets

• news

• news_comments

• organizations

• page_view_log

• people

• people_emails

• tasks

• task_comments

• tickets

• tickets_logs

• tickets_messages

• ticket_attachments

• ticket_charges

• ticket_feedback

• ticket_slas