Join Types

This article provides some general information regarding the various join types you may face in developing a Query.

Detailed Information

When writing queries, it is fairly simple to retrieve information from one table. In many cases, you want to retrieve data from more than one table or specify criteria in your query from a second table. In these cases, you need to link at least two tables in one query. Working with multiple tables is almost as easy as working with one table.

A join enables you to retrieve data from two or more records or to specify criteria from more than one record. When you perform a join, you link records based on their common fields.

Table of Contents

Join types in PS Query

These two types of joins are used in PS Query : 1) Standard Join and 2) Left Outer Join                                                             
                       showing a join diagram, which shows in a Standard join returns data only when there is a matching record in both records. It also shows in a Left outer join it returns all of the rows in the first record (Record A). If there are no match in record B, additional data will be returned BLANK.

Back to Top

Standard Join

Standard join returns data only when there is a matching record in both records.

For example, the record A has 10 rows, but the record B only has 7rows that matched by common field (ID in this example) , the query after join returns 7 rows.

showing a visual representation of a standard join using example data.

Back to Top

Left Outer Join

Left outer join returns all of the rows in the first record (Record A). If there are no match in record B, additional data will be returned BLANK.

For example, the record A has 10 rows. After doing a left outer join, the query still shows 10 rows.

Showing a visual representation of a Left Outer Join using example data.

Back to Top

Basic Join Tips

  • Join only one record at a time and run the query to review the data before joining additional records.
  • Checking the data with small sample will help.
  • Do not mix join types. If you use Left Outer Join for the first join, the rest of the joins will be Left Outer Joins.
  • Use UM_ (University of Maine System)'s reporting tables where tables were already joined and applied business logics
  • If the query result is not what you are expecting, check "Belongs to" column (Left Outer Join)

Back to Top


Showing the query record with the criteria tab and belongs to column location highlighted.


Click Edit button for the criteria, and you can change under the drop-down box for "This criteria belongs to".
Showing the Edit Criteria Properties window with the this criteria belongs to dropdown highlighted.

Back to Top

Step by Step Instructions 

Please go to the Creating Record (Table) Join page for step-by-step instructions for joining tables

Join PS Query Group!

Have you joined the UMS PS Query User Group yet? You can ask questions or share tips and more.

Environment

  • PeopleSoft Campus Solutions (CSPRD) 
  • PeopleSoft Campus Solutions Reporting (CSRPT)
  • PeopleSoft Human Resources (HRPRD)
  • PeopleSoft Financials (FNPRD)
Print Article