Creating Left Outer Joins in PSQuery

To create a left outer join on a new query:

1. On the Query Manager search page (Reporting Tools, Query, Query Manager), click the Create New Query link.
   
2. On the Records page, search for the first (left) record for the left outer join.
   
3. Click the Add Record link on the same row as the record that you want to add.
   
   The Query page appears.
   
   
4. Select the appropriate fields to add to the query.
   
5. Navigate to the Records page.
   
6. Find the joining record and then click the Join Record link on the same row as that record.
   
7. Select Join to get additional fields only (Left outer join).
8. Complete the join.
   
   • If the Enable Auto Join preference is selected on the Query Preference page, click the record name to join with.
     
   • If the Enable Auto Join preference is not selected on the Query Preference page, click the OK button.
     
9. Define the join criteria.
   
   • If the Enable Auto Join preference is selected, the Auto Join Criteria page appears.
     You can clear the criteria from the query. You can also click the Add Criteria button to add or edit criteria or conditions. If no common keys are between the two join records, a message appears instead of the Add Criteria page.
     
   • If the Enable Auto Join preference is not selected, the Auto Join Criteria page does not appear.
     If you want, navigate to the Criteria page to add criteria to the ON clause of the outer join. The This Criteria Belongs To drop-down list box specifies where the criteria will appear. Select the alias that corresponds to this join record.
     
10. Define the effective date criteria.
    
    • For 2–3 tier client: If the joined record is an effective-dated record, the Effective Date Criteria page appears.
      You can accept the defaults or change them as desired.
      
    • For 4–tier PeopleSoft Pure Internet Architecture: If the joined record is an effective dated record, a message appears stating that an effective date criteria has been automatically added.
      Optionally, you can navigate to the Criteria page to change the defaults for this criteria.
      
11. To ensure that the left outer join finished successfully, navigate to the Query page.
    Text for the joined records confirms a successful left outer join.
    
12. (Optional) If the query has multiple joins to the same security record, which can be resource intensive and time consuming when it runs, select the Security Join Optimizer option to improve the performance of this query.
    
    
    HAPPY LEARNING :)