SQL - Part 5: Complex Filters


To combine logical operators to create a complex filter set, while creating a list of all products that need to be reordered AND are not discontinued AND whose name starts with G OR whose name starts with C.

Background Information

Suppose the character Spock from the Star Trek series asks you to filter a set of data. Logical beings such as the Vulcans can’t help but user logical operators from time to time, so either you’re on your own, OR you can read this tutorial AND learn how to create complex filters using the logical operators: AND and OR (AKA in AutoTag all conditions, and any condition). It is also pertinent to know that many different data types can be compared, even dates and times.


Inserting a Tag, ForEach Tag, SQL - Part 1: Basic Selection, SQL - Part 3: Filtering with Static Values, SQL - Part 4: Filtering with Parameter Values

Starting Template

This tutorial is a continuation of Part 4 of the SQL Wizard tutorials, so we will start with the SQL Wizard open in the template created in SQL - Part 4: Filtering with Parameter Values. If you don’t know how to open the SQL Wizard yet, you simply select the ForEach tag, and click the Wizard button in the AutoTag ribbon.


1. Drag Discontinued column in

First, we’re going to drag a new column to the Columns box. We’re going to be checking if the Products are Discontinued or not so bring in that column.

Show Me How!

2. Add a filter: Products.Discontinued is equal to False

Using the same strategy as in SQL - Part 3: Filtering with Static Values, add a filter to test this condition: if Products.Discontinued is equal to False.

With the other filter already in place from Part 4, the two filters work together such that all returned results must meet both conditions. This is indicated by the all in the statement “where all of the following conditions are true” In other words, the products must be under the reorder level AND not be discontinued.

Show Me How!

3. Add a group where any condition is true

Now we’ll add another group by clicking to add a group. This is similar to using parentheses in a mathematical expression. We are saying A AND (B OR C) so A must be true, and either B or C must be true as well. In other words, any of B or C must be true. In this case, B and C will be set in steps 4 and 5 respectively.

Show Me How!

4. Add a filter such that Products.ProductName starts with G

This is the same strategy as in step 2, and in Part 3 of the SQL tutorials again. Add a filter, set the left hand side to Products.ProductName, set the condition to starts with (which only applies to strings) and set the right hand side to G

Show Me How!

5. Add a filter such that Products.ProductName starts with C

See step 4. This final condition makes the entire expression look like X AND A AND (B OR C). The conditions made in step 4 and this step are mutually exclusive, so they won’t ever be (and don’t have to be) true at the same time, only one or the other has to be true.

Show Me How!

6. View returned results

Check the right panel of the SQL Wizard. You’ll notice every piece of data shown meets the criteria mentioned several times in this document.

Show Me How!

Quiz Yourself!

How many levels of groups can you create in the SQL Wizard filtering box?
Unlimited. I can just keep adding empty groups and they will stay there when I open the SQL Wizard again
Only 1 as shown in this tutorial
As many levels as I have conditions to fill them with.
About 5.
True/False: Filters can test any data type
No. Only numbers and limited operations on text.
Yes. SQL Wizard is all powerful. It can even compare files, pictures, and DNA.
Yes, with obvious limitations. Pictures cannot be compared, and with dates and times, it depends on how they are stored in the database.
No. Only numbers


You have completed this tutorial. We recommend taking a look at the SQL - Part 6: Joining Tables tutorial next!