Using sub-queries in filters

When filtering the fields of a query with the filter format, you are also enabled to use sub-queries to narrow down the result. The following are syntaxes that can be used in sub-queries.

Quantified predicate

A quantified predicate compares a value with a set of values.

• Syntax
  expression----+- = --+- SOME --+-- ( subselect )
                                +- <>  -+  ANY    ---+
                                +- ! = --+  ALL   ----+  
                                +- <   --+
                                +- >   --+
                                +- <=  --+
                                +- ! >  --+
                                +- >=  --+
                                +- ! < --+

  The subselect must specify a single result column and can return any number of values, whether they are null or not.

  • When ALL is specified, the result of the predicate is:
    • True if the result of the subselect is empty, or if the specified relationship is true for every value returned by the subselect.
    • False if the specified relationship is false for at least one value returned by the subselect.
    • Unknown if the specified relationship is not false for any values returned by the subselect and at least one comparison is unknown because of a null value.
  • When SOME or ANY is specified, the result of the predicate is:
    • True if the specified relationship is true for at least one value returned by the subselect.
    • False if the result of the subselect is empty, or if the specified relationship is false for every value returned by the subselect.
    • Unknown if the specified relationship is not true for any of the values returned by the subselect and at least one comparison is unknown because of a null value.
• Examples
  SELECT qty FROM sales WHERE qty>= ALL (SELECT qty FROM sales)
SELECT BUYERID, ITEM FROM ANTIQUES WHERE PRICE != ANY (SELECT PRICE FROM ANTIQUES);

EXISTS predicate

The EXISTS predicate tests for the existence of certain rows.

• Syntax
  - [ NOT ] EXISTS--(subselect)

  The subselect may specify any number of columns and,

  • The result is true only if the number of rows specified by the subselect is not zero.
  • The result is false only if the number of rows specified by the subselect is zero.
  • The result cannot be unknown.
• Example
  SELECT DISTINCT pub_name FROM publishers WHERE EXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type = 'business')

IN predicate

The IN predicate compares a value with a set of values.

• Syntax
  expression----+-- [NOT] IN --+-- ( subselect )

  In the subselect form, the subselect must identify a single result column and may return any number of values, whether null or not null.

• Example
  SELECT distinct pub_name FROM publishers WHERE pub_id IN (SELECT pub_id FROM titles WHERE type = 'business')

The following example explains how to apply a subquery when filtering a field:

1. Create a query named mainin in the catalog, add the table Customers and select the following columns: Customers_Customer ID, Customer Name, Customers_City, and Customers_Region.
2. Click Filter on the Query menu to open the Search Condition dialog.
3. Click the Add Condition button to add a condition line.
4. Click beside the field text box. In the Expressions dialog, select the column Customers_Customer ID, then close the dialog.
5. Select in as the operator from the operator drop-down list.
6. Click beside the value text box. In the Expressions dialog, click the Subquery tab. Select an existing query in the catalog to be the subquery. If you want to edit the selected query, click the Edit Subquery button. To create a new subquery, click the New Subquery button.

   Here, we create a new query named subin, add the table Orders, select the column Orders_Customer ID, and add a condition "Ship Via=Express Delivery" in the Search Condition dialog.

7. Click OK. The subquery subin will then be added into the value text box. Click OK to close the Search Condition dialog.

Now, the subquery subin will be applied to the filter when you build a report that uses the Customers_Customer ID column.