Creating Subqueries

In some cases, the target (selected in Step 1 of the query builder) necessary to create a query may not contain certain fields needed to achieve the desired search results. In these cases, it is sometimes possible to create a subquery to pull in the desired fields from a different target. In Impexium, this involves connecting two queries.

For more on building queries, see Creating a New Query.

How do Subqueries Work?

The subquery configuration compares the results of "Query A" (the primary query) to the results of "Query B" (the subquery) and returns only the values that meet the filter criteria for both. To achieve this, two queries are linked together by locating a unique identifier present in the results of "Query A" that is (or is not) also present in the results of "Query B."

Only certain fields count as unique identifiers. Their names will include the term "Id." The most commonly used unique identifier is Customer Id, but other examples include:

  • Contact Id

  • Id

  • Invoice Id

  • Order Id

  • Product Id

  • Substitute Id

Once an Id field is selected, two unique options will appear under the Boolean operator menu for that field: Is in (Subquery), and Is not in (Subquery). These options are only available for fields that are unique identifiers. This selection filters the results based on whether the value from "Query A" is also in the result set for "Query B," or the value from "Query A" is not also in the result set for "Query B."

When the linked queries are run, the resulting data will display using the result fields from "Query A."

Building Using the Subquery Configuration

Example: Let's say you wish to build a query that lists committee members who are also event registrants.
The "Committee Member" query target does not contain fields related to event registration, and the "Event Registrants" contain limited fields related to committee membership. Using subquery configuration, we can create a setup that unites two queries to obtain the desired result: A query on committee members that applies filters from an event registrant subquery and delivers a result set containing fields from the Committee Member target.

  1. Configure Queries to be Connected: To begin, ensure that the two queries to be connected exist (or create them).
    In the scenario mentioned above, a query using a "Committee Member" target and a subquery using an "Event Registrants" target can be used to configure a query that pulls committee members that are also event registrants.

  2. Configure the subquery: In the Query Builder, open one of the two queries selected.

    1. Scroll to the bottom and locate the Save as a New Query (Create a Copy) section. Enter a name into the New Query Name field and click Save as New.

    2. Under Step 2 - Define Filter Criteria, click the green Add icon .

    3. On the new field, click Select Field and select Customer ID.

    4. When creating a subquery, an ID field must be selected. This will be the common value the system compares between the connected queries. Once an ID is selected, two unique options will appear under the Boolean operator menu for that field: Is in (Subquery), and Is not in (Subquery). Choose the setting appropriate for your configuration.

      1. Is in (Subquery): The system returns values included in both the primary query and subquery. Values not returned by both queries will not be included in results. (E.g. Customers that did attend a particular event.)

      2. Is not in (Subquery): The system returns values not included in the subquery. (E.g. Customers that did not attend a particular event.)

    5. In the query drop-down field, choose the query you'd like to join in your subquery setup. Once the query is selected in this drop-down, note that the fields related to that query appear, indented, under the Is In (Subquery) field.

      In our example, "Committee Directory" is the primary query, and "List of Active Event Registrants" is the subquery. In the image below, notice that because "List of Active Event Registrants" is chosen as the subquery, fields related to it (such as Event > Name, Registrant Name, etc.) are automatically pulled over.

      These fields can be rearranged, edited, deleted, and added. When adding fields in this area, users will select from the query target of the selected subquery, not the primary query. Any changes made to the filter criteria here only apply to this search. The changes will not be reflected on the original version of the query.

    6. In the same row, locate Matching Field and click Select Field. The Select Field pop-up window will appear.

    7. In the Select Field pop-up window, select the Customer ID field.

    8. Under Step 3 - Select Result Fields, review the selected results fields. Add, remove, or modify fields as needed. Note that only fields from the query target of the primary query will be available for selection.

  3. At the bottom of the Query Builder, ensure that the query is saved under the new name set in Step A. If so, click Save or Save and Run. If not, locate the Save as a New Query (Create a Copy) section. Enter a new name and click Save as New. Do not save over the original version of the query.

  4. When running the query, any criteria marked as "Ask at Run Time" from both the primary query and the subquery will appear. The criteria from the subquery will be differentiated by a gray box.

Note: For best results, no more than two queries should be used in a subquery configuration. If it seems necessary to join more than two query targets, a custom report may need to be created. For assistance determining whether a custom report is necessary, please contact Impexium Support.

Note: Queries with an aggregate configuration cannot currently be used in creating subqueries.