Dependent Subqueries and Independent Subqueries
- 25 Apr 2024
- 3 minute read
- Print
- DarkLight
- PDF
Dependent Subqueries and Independent Subqueries
- Updated 25 Apr 2024
- 3 minute read
- Print
- DarkLight
- PDF
Article Summary
Share feedback
Thanks for sharing your feedback!
Subquery type is an advanced property of subquery exports and subquery filters in a Configurable Joins query. By default, subqueries are dependent: they depend on the base of the query and use the joins that are available from that base.
In special circumstances, the type may be changed to independent. Independent subqueries are used to construct an entirely separate query, of any base, the results of which will be returned for each row of the main query.
Dependent Subqueries
Dependent subqueries make it possible to export multiple values or aggregate data from related tables, or to filter records based on a one-to-many relationship.
Critically, a dependent subquery always relates in some way to the base of the main query. For example, a query on the Person base might include a dependent subquery export that concatenates decision-scoped data, but the decisions included in that export would necessarily belong to applications that in turn belong to the person record represented in a given row of the main query.
For additional information, please see Subquery Exports and Subquery Filters.
⭐ Best PracticeLeave the subquery type set to dependent when building most subqueries.
Independent Subqueries
Independent subqueries are used to reference data that are unrelated to the base of the main query. When the type is changed from dependent to independent, a new category and query base will need to be selected for the subquery. As with any query, the base will determine what each row of the subquery represents. Independent subqueries may be built on any query base.
Exporting Independent Subquery Results
Independent subquery exports include the same set of output options as dependent subqueries:
Concatenate
Aggregate
Dictionary
Rank
Existence
Formula
XML
JSON
The output determines how the rows of the independent subquery will appear in each row of the main query. For example, on a query with a base of Person, an independent subquery export could use a base of Form and a concatenation of Form Start Dates with a row limit to list the dates of the next three events in a particular folder:
Each form that meets the filter criteria on the independent subquery would be included in the concatenated list, unless a row limit is set. By including a row limit of 3 and sorting the independent subquery by start date, only the start dates of the top three forms would be included in the list.
✨ TipBecause it is possible to join from Person to Form Responses and from Form Responses to Form, a dependent subquery is sufficient to export data about events for which a person has registered. An independent subquery is needed in order to include data about an event for which a person has not yet submitted a form response.
Filtering by Independent Subquery Results
Independent subqueries can also be used to limit the rows of the main query when an aggregate of comparison is selected. For example, in a query on the Person base, an independent subquery filter with a base of Alumni Volunteers can be used to compare the active address regions of volunteers to the active address regions of person records. In this case, person records would only be returned in the query results when they have an active address in a region where an alumni volunteer also lives:
✨ TipRename all joins to be descriptive. In this example, in order to compare person records to volunteer records, two separate joins were needed:
a join on the main query from Person to Address by Rank Overall, with a rank of 1
and a join in the independent subquery from Alumni Volunteers to Address by Rank Overall, with a rank of 1.
Renaming these joins to Person Active Address and Volunteer Active Address helps distinguish between them when selecting the two exports that should be compared in the subquery filter.
Was this article helpful?
