Intersection Queries

Introduction

Note: For simplicity, this Guide assumes that you are making a report with two queries, but InfoView supports many more than two.

Diagram showing how intersection queries work

Intersection queries ask InfoView to complete two queries, compare the results and present only the result rows returned by both queries. These work very similarly to Union Queries, but instead of adding, the results compared. The order of your queries is not important.

Like Union and Minus Queries, all queries involved in your Intersection must contain the same number of Result Objects and the order of the Result Objects matters. InfoView will be comparing the results of both queries in order to perform the final selection. If the result rows of the second query are drastically different from the first, for example because the columns are out of order, no rows will be returned because there will not be any overlap.

Video

Step-by-Step Guide

Example: Fictitious Graduate Students by Major, Registered by End of Term and Third Week

This report looks at fictitious graduate students registered in a major by the end of the term, but who had also registered by the third week of the same term.

The first query will return those students registered in the End of Term data extract. The second query will return those students registered in the 3rd week extract. The intersection query will remove those names that don't match between them and leave only the names of students who had registered in both.

Note: For privacy reasons, the data used in this example is not real.

Building Intersection Queries

1. Start a new report.

2. Write your first query: select your result columns and define your filters.

Example: Fictitious Grad Students

Combined query 1 pulls the data for the End of Term time period.

Screenshot of InfoView example intersection query 1

3. Click on the "Add a Combined Query" button in the top row of the query panel.

Screenshot of InfoView showing the location of the combined query button

Note how a box pops in below your list of objects and that "union" is selected by default. The two Combined queries are your two queries and you can toggle between them by clicking on the names.

Screenshot of InfoView showing additional combined query box in the query panel

Note also that Combined query 1 and Combined query 2 both have exactly the same Result Objects selected by default, but that the filters are different.

4. Double click on the word "union" to change it to "intersection".

Screenshot of InfoView showing where to click to change from a union to an intersection query

5. Complete your second query: the result columns from your first query are pre-selected but can be replaced with others, including empty filler cells. Replacing the columns is not recommended because the comparison of your results queries will be looking for exact matches.

Example: Fictitious Grad Students

Combined query 2 pulls the data for the Third Week time period with all of the same result objects.

6. Complete your second query: define your filters.

Note: If both of you queries are filtering on the same value via a prompt, you'll need to be very careful. Both queries need to have a filter with exact same prompt text (the value that appears in the text box) in order to unify into one single prompt, otherwise the field will present two separate prompts in the prompt window.

Example: Fictitious Grad Students

Combined query 2 pulls the data for the Third Week time period.

Screenshot of InfoView example query 2

7. Run your report.

Query 1 (End of Term) results as a single query:

Screenshot of InfoView example query results for end of term

Query 2 (3rd Week) results as a single query:

Screenshot of InfoView example query third week results only

Intersection of Query 1 and Query 2:

The results of this intersection are exactly the same as third week, showing that no one has dropped the class between the Third Week and End of Term extracts.

Screenshot of InfoView example query third week results only

Tips and Tricks: Nesting Unions, Minus and Intersection Queries

Union, Minus and Intersection queries can be nested in the same way that filters can be nested with ANDs and ORs (see the Nesting Guide for more information.)

The button for nesting is in the top right corner of the combined query box:

Screenshot of InfoView combined query nesting button location