Multiple Queries As Subqueries

Introduction

Diagram showing how subqueries work

Multiple queries can be used instead of subqueries for situations where the data source for the subquery is different from the main query or the subquery should limit more than one column in the main query.

Very Important Note: The subordinate query must return fewer than 1,000 rows, otherwise this method will not work.

Video

Multiple Queries as Subqueries

Prerequisite: You should be familiar with both subqueries and multiple queries at least to part 1. See the Subquery Guide and Multiple Query Guide Part 1 for more information.

Note: You do not specifically need to be in Java Mode to use this method.

Example: Building Age for a Particular Class

If we want details about a building from the Facilities universe in which a particular class took place over time, we can use a subquery to limit our facilities query to only those buildings used for that particular class over the years. We can't use a normal subquery, because the Facilities universe does not have any course information in it: we can't tell from the facilities universe which buildings were used for the class. However, we can pull this information from the Course Review universe and use the results of that subquery to limit our main Facilities universe query.

0. Know your data. Just like in Multiple Queries, you'll have to know your data very well. You need to know what field from your subquery matches with which field in your second.

Example: Building Age for a Particular Class

Just like in Multiple Queries, I know that CS Bldg Code in the Course Review universe and B Building Name in the Facilities universe overlap. I'll use the Course Review field to limit my Facilities query on the corresponding field.

1. Navigate to the Query Panel

2. Leave this first query completely blank. Click on the "add query" button and select "From Universe", then the second data source.

Note: If you're in Java Mode, you can pick "From Excel" and query from an excel file. See our Excel Guide for more information.

Screenshot of InfoView showing the location of the add query button

3. Construct your main query as Query 2. Select all of the desired result objects. Complete all of the filters except for the one that you want to tie to a subquery.

Note: It is helpful to make your main query Query 2 because subqueries must be completed first before they can limit your main query. InfoView works through the queries in order. The order of your queries is not critical: you may just have to run your query twice before data is returned.

Example: Building Age for a Particular Class

We could pull in all the detail that we might want from the Facilities universe, but for simplicity I have chosen the Building Name, Number and Age. The Query Filters panel is currently blank; the limit from the other query will be added later.

Screenshot of InfoView mutliple queries as a subquery example for the main query

4. Go back to your first query by clicking on the tab labeled "Query 1" at the bottom of the query panel. Complete your subquery as Query 1. You can pick as many objects as you want but the object that you want to limit on must be in the result objects.

Tip: Limit your subquery as much as you can because this method only works for subquery results under 1000 rows.

Example: Building Age for a Particular Class

Since we are only creating one limit from this query, we only need that one result object. If we wanted to limit based on more results from this query, we could add more result objects.

Screenshot of InfoView mutliple queries as a subquery example for the subquery

5. Toggle back to your main query in Query 2.

6. Add the object as a filter on query 1 that you want to use with the subquery. Change the operator if you so desire.

7. In the Filter Options menu, select "Result from another Query".

Example: Building Age for a Particular Class

Screenshot of InfoView mutliple queries as a subquery example showing the filter options menu

8. Select the object from your other query that should limit your first query.

Note: If you don't see anything in this list, you'll have to run your queries first so that your subquery has information in it. Fill in your filter with a "dummy" constant (it doesn't matter what--your main query does not need to return any results) and run your queries. Once you have results, return to the Query Panel and complete step 8 again. You should see the result objects from your first query in this list.

Example: Building Age for a Particular Class

Screenshot of InfoView mutliple queries as a subquery example showing the selection list

9. Run your report.

Troubleshooting: If your subquery is the second query, you may get a message saying that there's no data. Refresh your report again; it should populate.

Note: The results of your subquery will also display with the results of your main query, meaning that you can add more detail. You can also make your subquery contents prompts. However: remember the 1,000 record limit!

Example: Building Age for a Particular Class

The above queries return the following results, showing all of the buildings that the class POLI 190H has ever been held in and the details relating to those buildings:

Screenshot of InfoView mutliple queries as a subquery example showing results