Overview:  

Subqueries are used to enhance reporting.  They can be used to validate enrollment in a certain class (exists), to make sure a student isn't already in a student group (does not exist) or to position a record on the main query on a unique record - the maximum term of STDNT_CAR_TERM or the minimum plan sequence on ACAD_PLAN.  While a subquery may help the query narrow down the selection of data, the fields from the subquery table(s) are not available in the main query and cannot be part of the output.

What is a sub-query:

A subquery is a mini-query or an inner query.  It cannot stand alone but it can help to limit the selection of data.  Just as other statements in the Criteria tab, a subquery will narrow down the data selected in the main query.  There are three types of subqueries:

  1.  Condition Type is 'exists';
  2.  Condition Type is 'does not exist';
  3.  Condition Type is 'equals'. 

The following Images show the step by step process:

Go to the Criteria tab.  Click the yellow Add Criteria button. 

When the Edit Criteria box opens, change the Condition Type to does not exist if you want to query to eliminate a row when a certain condition is true (i.e., a passing grade on a test, enrollment in a class, etc.).  Or choose exists when the query needs for the condition to be true (i.e., active in a student group).  It can also remain equals if you want the subquery to return a value which should equal Expression 1. 

Click on the Define/Edit Subquery button.

The system will prompt you for record which is needed for the subquery.  You will notice that the tabs for the subquery are the same as the main query.  This is the same a building a query - you can use prompts, create expressions, build criteria, etc.  Unlike the main query where Query creates some of the joins, you will need to do your own join here below.

In subquery you can select only one field with main query.

This subquery is insuring that the student (A.EMPLID) does not have either COM 8191 or ENG 8192.  The joins between the A record and the F record, in this case CLASS_TBL_SE_VW, must be inserted manually via thCriteria tab.  Then hit Save button.

To navigate back to the main query, click on the Subquery/Union Navigation link (upper right side of the page).  Then click on the Top Level of Query, or another level, if desired.

 

 

 

  • No labels