Creating Record (Table) Join

This article provides step-by-step instructions on how to join tables in PS Query.

Detailed Information

Table of Contents

INFO: For more information regarding the basic concept and types of joins, please read the Join Types TDX article.

Types of Join 

To assist users in using query joins, PeopleSoft delivers several predefined joins. Because these types of joins are predefined, you do not have to add any criteria to manually link the records.

  • Record Hierarchy join has a one-to-many relationship. They use records that are parents or children of each other. A child table is a table that uses all the same key fields as its parent, plus one or more additional keys. The parent record in the PeopleSoft Application Designer defines the hierarchical relationship.
  • Related Record join has a one-to-one relationship. They use records from non-hierarchical records that are related by common fields. The prompt table edit defined for a field in PeopleSoft Application Designer determines the relationship between the records.

Not all records have a parent/child relationship, or an automatically joined record may not work as you wish. Query Manager allows you to join any table. 

  • Any Record join must be done manually. It will attempt to automatically show join criteria, which may or may not be correct. It is very important to understand the record structure. This is recommended for joining tables.

Back to Top

Procedure

In this example, the Records Office staff has asked you to generate a report with class meeting information. You will use three records to build this query utilizing a record hierarchy join and a related record join.

Find tables before you start creating a new query. If you don't know the table name, see the Finding Your Data page for finding the table. In this example, CLASS_TBL will be added first (Record A), and then CLASS_MTG_PAT table will be joined (Record B). Additionally, FACILITY_TBL (Record C) will be joined.

Add the first table (Record A)

  1. Go to Query Manager in CSPRD or CSRPT (Read Running Reports with Query Viewer & Query Manager page for more information if needed)
  2. Click the Create New Query Link

showing the location of the create new query link in the Query Manager.

  1. The first step in creating a query is to find an existing record for the query. In this example, you will locate and use the Class Table record.

    Enter the desired information into the Description field. Enter "CLASS_TBL".
    Click the Search button.

Showing an example of performing a record search for records beginning with CLASS_TBL.

  1. Click the Add Record link for CLASS_TBL.

Showing the location of the add record button.

  1. Next, select the fields from the table. Check the box for the SUBJECT, CATALOG_NBR, DESCR,  and CLASS_NBR fields.

showing the chosen records screen with four fields selected.

  1. Save the query before going to more steps.
Back to Top

Add a table using Hierarchy Join

If the hierarchy join is not available, or you prefer to join a table manually, skip to the Add table using Any Record Join section (step #10)

  1. In this example, you next need to select fields related to meeting information from the Class Meeting Pattern Table record. To join records that share a common high-level key, simply select the Hierarchy Join link. Click the Hierarchy Join link.

    Showing the location of the hierarchy join button.

  2. A new page appears that allows you to select the record to be joined to your existing query.

    Note that the hierarchy on this page is not related to the hierarchy of the Dictionary Tree. Rather, the hierarchy shown is defined in the PeopleSoft Application Designer with the Parent Record Name feature.

    Click the CLASS_MTG_PAT - Class Meeting Pattern Table link.

  3. Now you see two tables in the query.

Back to Top

Add a table using Any Record Join

Records beginning with UMS will typically not have children or parent tables identified as in the above image and must be joined manually. See steps #10 - #12 to manually join a table.

  1. Click on the Records tab and search for the CLASS_MTG_PAT table. Click Join Record.

Showing a record search for CLASS_MTG_PAT and the location of the join record button.

  1. Choose an appropriate join: Standard Join or Left Outer Join (Read Join Types page for more information).  In this example, Standard Join was selected.

Showing the selection of a standard join type.

  1. Query Manager will detect and add the join criteria automatically. Click Add Criteria.
INFO: This automated list may or may not be correct all the time. Please review carefully.

Showing the Auto Join Criteria menu and the add criteria button location.

Back to Top

First join is completed

Regardless of which method you use to create your join, your newly joined record and its fields are displayed below the first record. Notice that each record added to your query is assigned an incremental letter that represents a correlation, or alias, of the record. The second record denotes that it was joined with the first record. In this example, CLASS_MTG_PAT (B) was joined with CLASS_TBL (A).    

showing that a new table has been added to the query listed in the query tab.

When you used the hierarchy join, the join criteria will not show on the Criteria page.

Showing an image of a Criteria page for a hierarchy join.

However, if you View SQL, the join criteria is showing the page:

  Showing the View SQL tab for a query using a hierarchy join and highlighting the join criteria in the SQL.

When you used Any Record Join, the join criteria will show on the Criteria page.
  Showing the criteria tab for a query with any record join.

Back to Top

Add fields from the joined table

Now you can select the fields from the joined record.

  1. Go to the "Query" tab and make sure the table B is expanded.

showing the query tab with table B expanded.

  1. Check the box for MEETING_TIME_START, MEETING_TIME_END, and STND_MTG_PAT fields. If you don't see the fields. Scroll down using the vertical scrollbar or go to the next page by clicking the circle arrow icon. You can also click the A-Z icon to sort the field name.

    showing the query tab where you would add more fields to the query.
Back to Top

Related Record Join

Related records are specific to a field in the current record. If a field has a related record, you will see the record displayed as a hyperlink next to the field.

  1. Next, for this example, you will select the Facility Description field from the Facility Table record. Click the Join FACILITY_TBL link.

showing the location of the related record join link.

  1. Choose an appropriate join: Standard Join or Left Outer Join (Read Join Types page for more information).  In this example, Standard Join was selected.
    Click the OK button.

Showing the select join type window.

  1. A pop-up window regarding the Effective date will show up, and click OK

    showing the effective date pop-up window message.

If you go to the Criteria tab, you can see the effective date criteria.

showing the criteria window with the effective date listed.

Notice that the newly joined record appears below the other two records and has been given the alias of “C”. To learn more about the Effective date, please go to the Effective Date page.

  1. On Query tab, you now see three tables. You can add the DESCR field from table C.
    Showing the query tab with three tables listed.

Back to Top

Check the Query

  1. Go to the Fields page and make sure all the fields you wanted are listed.
    You can edit the query, such as:

  1. Run the query to view the results of the query.

Showing the location of the run tab in the query manager.

If you aren't satisfied with the results, check the criteria or other pages. You can also change the join type or join procedures to test the query.

  1. Don't forget to save the query. Read the  Sign/Comment section on the Edit Queries page for more information. 
​​​​​You have successfully joined 2 additional tables.
Back to Top

Environment

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