Cross-referencing table fields

DPQL Reference

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

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.

../_images/xref-example.png

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

Note that the cross-reference field you use is different from the name of the actual table you’re cross-referencing. For example, you cross-reference using ticket, but the table name is tickets. Table names are plural, but the cross-reference name is singular.

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

Published: 16/03/2017

Last updated: 16/03/2017