How to use sub-queries
Sections in this article
How Parishsoft IQ processes sub-queries
- When you want to know if a certain set of records exists
- When you don't have enough information
- When you want to exclude a set of records
What Is a Sub-query?
A sub-query is a query that is inside another query. A sub-query is oftentimes referred to as an "inner query", and the query that contains it as a main or "outer" query. This terminology may be slightly misleading because implies two levels only: an outer and an inner. In fact, Parishsoft IQ imposes no limit on the levels of sub-queries a query can have. It is possible for a sub-query itself to include other sub-queries to any number of levels. However, when it comes to performance, the more nested levels a query contains, the longer it takes Parishsoft IQ to process the query and display results.
The diagram below shows the concept a sub-query and the relationship between it and the main query. The sub-query is self-contained and independent of the outer query. It is, in fact, evaluated separately as if it were a standalone query. Parishsoft IQ processes the sub-query once and then passes the subquery's value to the main query, where it is used as a condition to restrict the data the query retrieves.
How Parishsoft IQ processes sub-queries
Parishsoft IQ always processes a sub-query construct from the bottom up. In other words, it evaluates the bottom-most sub-query first and then works its way up through all of the sub-queries. For each sub-query, Parishsoft IQ actually performs two operations: runs the sub-query and then passes the resulting value to the sub-query at the next level up. This process is repeated at each level. After the top-most sub-query finishes processing, it inserts the final value into the main query. The main query compares that value against all values in the selected columns, and then displays the results. The following diagram illustrates the order of operations.
When to use a sub-query
A sub-query is an efficient tool for helping you solve data retrieval problems. A sub-query is commonly used when:
● You want to know if a certain set of records exists.
● You don't have enough information.
● You want to exclude certain records from your results.
How to construct a sub-query
Probably the most challenging aspect of the design entails figuring out whether you need a sub-query and, if you do, what part of the design needs to be the sub-query. Start by breaking up your question to figure out exactly what information you need. Match the information to columns in your database. To help you understand the process, let's analyze a question to see how you might design a sub-query out of it.
Ask the Question
What are the names and addresses of families living in the same zip code area as Henry Abing?
Analysis
Break down the question by mapping it to elements in the Column Picker panel. As shown in the illustration below, the first part of the question, "What are the names and addresses of families", maps to columns in Families group, specifically, the Last Name column and columns in the Addresses subgroup. The second part of the question, "zip code area" maps also to a column in the Families group, specifically the Home PostalCode column.
Upon closer analysis, all of the elements in the first part of the question relate to your family (census) data. This part of the question looks like a good candidate for the main or outer query because it simply references the columns of information needed in the result set. The second part of the question is focused on just exactly what you need: a zip code. The zip code appears to be a good candidate for the sub-query (inner query) for two reasons:
● The value exists in your database, but you don't know the exact value. The sub-query can retrieve the value.
● The value retrieved by the sub-query can be used in the main query to limit the records it selects.
We learned earlier that Parishsoft IQ processes the sub-query first. It will return a zip code match for Henry Abing.
Upon finding the match, the sub-query will pass the zip code value to the main query. The main query will search the Families > Addresses > Home PostalCode column to find families with the same zip code. The names and addresses of matching records will appear in the results set.
You can now design one big query that answers the question. To view the details for this query, go to When You Don't Have Enough Information.
Sub-query Examples
The best way to understand sub-queries is to look at examples of when you can use them. Following are some examples of situations in which using a sub-query can help solve a specific type of problem.
When You Want to Know If a Certain Set of Records Exists
A sub-query can be used to find out whether or not a particular set of records exists in your database.
Scenario
Suppose you are wondering whether there are any non-registered families in your organization with children currently enrolled in your Religious Education classes. You could design a sub-query that checks for the existence of such families.
Question
What are the last names of non-registered families that have children currently enrolled in Religious Education classes?
Query Setup
After analyzing the question, you determine that the main query should reference these columns and display their values as output:
● Families > Last Name
● Families > Family DUID
You also determine that you need to apply these sub-query conditions to limit the results returned by the main query:
● Families > is Registered is equal to No
● Religious Education School Year is equal to 2015 - 2016
Below is a sample setup for this query:
This example uses the Family DUID and Family Last Name columns to the main query. Depending on the information you need, you could add other columns. For example, if you wanted to email families, you could add the email address column.
When you don't have enough information
Sometimes, you don't have enough information to find the records you need. The following scenario presents a situation in which a sub-query can find the records you are looking for.
Scenario
Suppose that one of your members, Henry Abing, had a nasty fall and broke his leg. You want to find out the names of families living in the same zip code area so that they can bring him meals while he recuperates.
Question
What are the names and addresses of families living in the same zip code area as Henry Abing?
Query Setup
After analyzing the question, you start the setup by selecting these three columns of data:
● Families > Last Name
● Families > First Name
● Addresses > Home PostalCode
● Families > Contact Info Primary Phone
Next, you know that you need to specify a condition on the Home PostalCode column to limit the records the query retrieves to those with the same zip code as Henry's. Unfortunately, when you reach the point of entering the zip code as a condition for the column, you realize that you do not know it.
Luckily, you don't have to run around to try to find the zip code and then enter it manually. You can use the information you have to find the information you need by designing a sub-query.
To find Henry Abing's zip code:
-
In the Query Conditions panel, edit the Families.Addresses Home PostalCode condition as follows:
a. Click the is equal to link and select in sub query.
b. Click the [edit sub-query] link to open the Edit sub-query window.
-
Set up the sub-query to look like this:
-
Click OK to close the Edit sub-query window.
When you run the query, it returns Henry Abing's zip code.
Now, when you run the entire query, Parishsoft IQ takes a look at the sub-query first, evaluates the condition, and then passes the zip code value to the main query to find the names of all families whose zip code matches the value received from the sub-query. The names are displayed in the Query Results panel:
In this example, five families live in Henry Abing's zip code area. One of those records is also Henry Abing's so there are four families that can be contacted to help with meal deliveries.
When You Want to Exclude a Set of Records
In certain situations, you may need to know who is not in a particular group so that you can communicate directly with those individuals. You can use a special sub-query construction to find out this information.
Scenario
Suppose you want to do a special mailing to registered families in your organization that have children aged 5 -16 who are not currently enrolled in your Religious Ed. program. You want to invite this group of families to a special open house to tour the facilities and meet the staff.
Question
What are the names and email addresses of registered families with children aged 5 to 13 in Holy Name that do not have their children enrolled in Religious Education classes?
Query Setup
After analyzing the question, you determine that the main query should reference these columns and display their values in your results output:
● Families > Family DUID
● Families > Last Name
● Families > Contact Info Email address
Depending on the type of question, it is sometimes easier to design a sub-query to retrieve the records you do not want, in this case: the names of families with children in the specified age range for which the enrollment condition are true. Then, apply the not in sub-query condition to force the main query to exclude these records from the results set. What you are left with are records you do want: those that are the opposite of the condition.
Updated