Common Subquery Patterns
Subqueries are useful to collapse several data points into a single field. Below are some examples of common design patterns to make developing your own queries easier.
Accessing the display field of a lookup that is a subquery
Contacts Table
Field | Type | Configuration |
---|---|---|
first_name | Single Line of Text | |
last_name | Single Line of Text | |
full_name | Subquery | CONCAT(first_name, ' ', last_name) |
Invoice Table
Field | Type | Configuration |
---|---|---|
invoice_number | Single Line of Text | |
contractor_id | Lookup | Looks up to Contact with display field of Full Name |
invoice_identifier | Subquery | See Below |
The goal of this query is to display the Invoice Identifier in the format {Contact Full Name} : INV{Invoice Number}
This can be achieved using
SELECT CONCAT(C.first_name, ' ', C.last_name, ' : INV', t.invoice_number)
FROM dbo.contact as C
WHERE C.id = t.contractor_id
AND C.deleted IS NULL