Working with Subqueries

A subquery, sometimes called a sub-SELECT, is a query whose results are used by another query. The main query uses the subquery’s result set as a comparison value for a selection criterion.

You create a subquery when you need to compare a field value to the results of a second query. Suppose, for example, that you want a list of employees who are not members of any professional organizations. For each employee in the PERSONAL_DATA table, you must determine whether his or her employee ID is in the MEMBERSHIP table. That is, you must compare the value in the PERSONAL_DATA.EMPLID field to the results of a subquery that selects the EMPLID values from the MEMBERSHIP table.

This example illustrates the fields and controls on the Criteria page when you are using a subquery.

Criteria page when you are using a subquery

This example illustrates the fields and controls on the Query page when you are creating a subquery.

Query page when you are creating a subquery

To create subqueries:

  1. Select Reporting Tools, and then Query, and then Query Manager.

  2. Click the Use As Criteria icon on the Query page, or click the Add Criteria button on the Criteria page.

  3. On the Edit Criteria Properties page, select Subquery as the comparison value.

    PeopleSoft Query displays a special Query Manager view where you can select a record. See the example of the Query page with subquery above.

  4. On the Query - Subquery page, select the required field.

    Note:

    A subquery can have only one field. However, you can create multiple subqueries.

    When you have defined subqueries and unions, a Subquery/Union Navigation link appears at the top of each page of the main query and the subqueries.

  5. Click the Subquery/Union Navigation link to display a hierarchical view of the main query and all subqueries and unions.

    Use the hierarchy to navigate between the main query, subqueries, and unions.