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:
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.
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:
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: