Dependent Subqueries and Independent Subqueries

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 Practice

Leave the subquery type set to dependent when building most subqueries. Dependent subqueries most closely resemble the subquery exports and filters available through the Slate Template Library, such as the export on the Prospects base for Events - Comma Separated or the filter for Has Application by Round.

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: 

independent_sub_export.JPG

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.

  Tip

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

independent_sub_filter.JPG

  Tip

Rename all joins to be descriptive.

In this example, in order to compare person records to volunteer records, two separate joins were needed:

  1. a join on the main query from Person to Address by Rank Overall, with a rank of 1
  2. 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?
7 out of 10 found this helpful