This topic explains the concept of how to group conditions using the logical operators and and or. Grouping conditions enables you to design a query that makes multiple comparisons of the data. An understanding of the concept of grouping conditions and the examples presented in this topic can help you fine-tune your queries and make them more efficient at finding the exact data you need to answer your questions.
The And and Or Operators: a Review
Grouping Condition Using And and Or
In the where clause, the and and or operators are used to combine multiple conditions to narrow the scope of the query so that you can find the records you are looking for.
The and operator lets you test for two or more conditions. Previously, we looked at queries like the following in which the where clause defines multiple conditions using the and operator:
To review, the and operator allows you to specify as many conditions as you need to locate the records you are looking for. However, it must be noted that all conditions separated by and must be true for a record to be included in the result set. The and operator is restrictive and therefore limits your search. In the above example, for a record to be retrieved from the database it must satisfy each one of these conditions:
● Between the ages of 18 and 35
AND
● Male
AND
● Unmarried
The or operator also lets you test for two or more conditions. The following illustration shows a where clause in which multiple conditions are joined by or:
Like and, the or operator allows you to specify as many conditions as you need to locate the records you are looking for. However, the or operator is considerably different from and in that it requires that only one of the specified conditions be true for a record to be included in the result set. The or operator is more inclusive than and and typically broadens a search. In the previous example, the result set includes records that meet at least one of the following conditions (although more than one or all can be returned):
● Between the ages of 18 and 35
OR
● Male
OR
● Unmarried
The previous examples implement multiple conditions using the and or or operator. ParishSOFT IQ allows you to design more complex queries using the and and or operators together in a where clause.
Grouping conditions enables you to design highly selective and efficient queries to search your database for records that meet very specific requirements. Let's explore some examples to illustrate how you can group conditions using both operators in a where clause.
To learn about grouping conditions, look at this example:
Suppose you want to find the names of members who live in the 48189 or 48300 zip code area but who also reside within the Whitmore Lake city limits.
This example is quite simple, but there are times when manually writing the structure of the where clause can be helpful so you can see what information you need and how you want the conditions to be grouped.
Inside the brackets, we have two conditions separated by the or operator. If a record satisfies either of these conditions, it passes the first check. To be included in the result set, however, the record must also satisfy the ANDed condition. So, as this example shows, you can use the or operator to provide options and combine those options with the restrictive nature of the and operator to make your queries a little more robust.
The following instructions show you how to set up the grouped conditions in the previous example.
In the Query Conditions panel, click 
(Group Conditions button).
As shown in the following illustration, this clause appears under the Select records statement: any of the following apply.
Because the any keyword has an "or" meaning, leave the added clause as is.
Add the first grouped condition. To do this, complete the following:
Click the Batches Actual Cash Total link. From the list, select Members > Contact Info > Home Zip.
Click the starts with link. Then, from the list, select is equal to.
Click the [enter value] link and type 48108 in the text field.
Add the second grouped condition. To do this, complete the following:
Click 
. From the list, select Members > Contact Info > Home Zip.
Click the starts with link. Then, from the list, select is equal to.
Click the [enter value] link and type 48300 in the text field.
Add the ANDed condition. To do this, complete the following:
Click the Add new condition link. From the list, select Members > Contact Info > Home City.
The system adds the condition. Note that the keyword and precedes the condition.
Click the starts with link. Then, from the list, select is equal to.
Click the [enter value] link and type Whitmore Lake in the text field.
The query conditions setup now looks like this:
Understanding Query Conditions