This topic explains the concept of subqueries and shows examples of how to create them.
How ParishSOFT IQ Processes Subqueries
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
A subquery is a query that is inside another query. A subquery is often times 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 subqueries a query can have. It is possible for a subquery itself to include other subqueries 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 subquery and the relationship between it and a main query. The subquery 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 subquery 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.
ParishSOFT IQ always processes a subquery construct from the bottom up. In other words, it evaluates the bottom-most subquery first and then works its way up through all of the subqueries. For each subquery, ParishSOFT IQ actually performs two operations: runs the subquery and then passes the resulting value to the subquery at the next level up. This process is repeated at each level. After the top-most subquery 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.
A subquery is an efficient tool for helping you solve data retrieval problems. A subquery 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.
Probably the most challenging aspect of the design entails figuring out whether you need a subquery and, if you do, what part of the design needs to be the subquery. 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 subquery out of it.
What are the names and addresses of families living in the same zip code area as Henry Abing?
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 subquery (inner query) for two reasons:
● The value exists in your database, but you don't know the exact value. The subquery can retrieve the value.
● The value retrieved by the subquery can be used in the main query to limit the records it selects.
We learned earlier that ParishSOFT IQ processes the subquery first. It will return a zip code match for Henry Abing.
Upon finding the match, the subquery 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.
The best way to understand subqueries is to look at examples of when you can use them. Following are some examples of situations in which using a subquery can help solve a specific type of problem.
A subquery can be used to find out whether or not a particular set of records exists in your database.
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 subquery that checks for the existence of such families.
What are the last names of non-registered families that have children currently enrolled in Religious Education classes?
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 subquery conditions to limit the results returned by the main query:
● Families > is Registered is equal to No
● Religious Education SchoolYear 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.
Sometimes, you don't have enough information to find the records you need. The following scenario presents a situation in which a subquery can find the records you are looking for.
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.
What are the names and addresses of families living in the same zip code area as Henry Abing?
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 subquery.
To find Henry Abing's zip code:
In the Query Conditions panel, edit the Families.Addresses Home PostalCode condition as follows:
Click the is equal to link and select in sub query.
Click the [edit sub-query] link to open the Edit sub-query window.
Set up the subquery to look like this:
Click 
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 subquery 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 subquery. 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.
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 subquery construction to find out this information.
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.
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?
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 subquery 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 subquery 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.
The setup looks like this:
Understanding Query Conditions