Did you know that the subquery functionality within PS Query can be used to exclude specific values of a field where more than one may exist? Some student-records-related examples of this are student groups and service indicators. As many have experienced, if an attempt to exclude students who have a specific service indicator or student group is made at the top level of a query, many students who meet that exclusionary criteria will still be identified because they have another distinct value of that same field.
After the query is written to the desired specifications, criteria should be added on the student EMPLID field. The criteria Condition Type should be ‘not in list’, selecting Subquery in the Expression 2 Type box.
The subquery is defined and, unlike last month’s subquery example, is a completely distinct query and does not need to be joined back to the top level. The field added to this subquery is EMPLID, as the purpose is to exclude students from a list of EMPLIDs. No aggregation should be added.
This particular query is excluding students who have at least one value from a specific set of student groups on their record in the SIS, in which the effective status (EFF_STATUS) is equal to ‘A’.
Easy as that. Navigate back to the top level and be sure to save. It is suggested to run the query before the subquery is added and again after, to ensure that the subquery did what it is supposed to. It is always suggested that data verification is performed before passing data or a query on to another user!
Next month: Follow-up long blog article on Subqueries