Subqueries

Definition

Diagram showing how subqueries work

A subquery, also known as a nested query, is a way to filter your report via a second query. You never see the results of your subordinate query directly: it only serves to limit your primary query. Constructing a subquery means selecting an object to filter on, as usual, and limiting the filter with other, sub-query specific filter logic. This is particularly useful if you want your filter to be dynamic and based on criteria that are different from the filter criteria in your main query.

Subqueries always use the same data-source as the main query. To construct subqueries using different data sources, check out the Multiple Queries as Subqueries Guide.

Subqueries always only limit a single field in your main query. Depending on your business need and the data that you’re working on, this single field constraint can be an issue: In order for this limit to be meaningful, it must be sufficiently unique for your report purposes. If it is not, you must make it so with other filters. Objects used as unique identifiers, such as student ID or employee ID numbers, or concatenated fields such as subject – catalog number – section number or a full name including middle initial, are good choices for subqueries.

It is possible to use more than one subquery in a single report. You can filter by two separate subqueries or nest them, one within the other.

Video

Subqueries

1. Get to the Query Panel by starting a new report or editing an existing report.

2. Select the object in your universe that you would like to filter on with a subquery.

3. Click on the subquery button in the button selection area of your Query Filters.

Screenshot of InfoView query panel query filters bar in java mode

Example: Filtering on CS Course

This is similar to what your subquery will look like by default:

Screenshot of InfoView subquery filter on field CS Course

The first "CS Course" field is the beginning of a normal filter. The second "CS Course" field is the result object of your subquery.

4. Usually, we want these two fields to be equivalent to each other, but, if desired, you can change the operator to something other than the default “In list”.

5. Add in query filters to your subquery to define which rows of that object should be included.

Example: Filtering on CS Course

This subquery will limit the course numbers to just those ART courses that have at least one section with an enrollment count of 100 or higher in Fall of the 2005-06 academic year.

Note: This is not measuring an enrollment total of over 100 for all sections taken together; a course will qualify only if it has at least one section with enrollment over 100. You will need to experiment and get to know your data to be sure that you are retrieving the information that you want.

Screenshot of InfoView subquery

6. Complete the rest of your report: add the desired result objects and any other filters to your main query.

Example: Filtering on CS Course

This query will return ART courses given in Fall 2006-2007 which were also taught in Fall 2005-06 and had at least one section with enrollment of 100 or more in that year.

Screenshot of InfoView showing full query filters with a subquery

Note: Both queries have been limited to a single year, a single department code and a single term to make sure that the values in “CS Course” are sufficiently unique. CS Course must be sufficiently unique because our subquery has only a single result object which imparts none of the context from the rest of the row. For example, many departments will have a course with the number “020”. If the filter limiting D Dept Code to ART were to be removed, the results would erroneously show the art class 020 because some other department had enrollment above 100 with that course number.

7. Once you are satisfied with your selections, run your report.

Addendum1: Object Matching

By default, InfoView assumes that you want the result object of your subquery to be exactly the same as the filter object. It is rarely necessary to change the result object to a different field, but it is possible.

If you would like to change the result object, select it and then click-and-drag it out of the subquery. You'll need to replace the field with something else before you run your query. Your choice must be a dimension or your report will not run.

Screenshot of InfoView with a subquery filter based on a different object

Addendum 2: Changing the filter field

If you notice that you’ve chosen the wrong field to filter on, you can replace that field by the same drag-and-drop method. When you do this, make sure that your new filter object is matching the correct subquery object on the right. Both must be filled out or your report will not run.

Screenshot of InfoView showing how to change the filter field of a subquery