Hierarchy Joins

In this topic you will learn how to create Hierarchy Joins.

A Hierarchy Join uses tables/records that are related through a parent-child relationship. A child table is a table that uses all the same key fields as its parent, plus one or more additional keys. Hierarchy Joins are the strongest of the three joins, and are predefined in the system. This means the parent-child relationships are already identified and defined within the system.

In this example, you will create a Hierarchy Join when the VENDOR_ADDR record (child) is joined to the VENDOR table (parent).

You will first search for the parent table/record.

From the VENDOR table, you will select the VENDOR_ID, NAME1 and VENDOR_STATUS fields.

By clicking the Hierarchy Join link, all of the records that have a parent/child relationship with the VENDOR table display.

The VENDOR table is the parent record. The VENDOR_ADDR record is a child of the VNDR_ADDR_SCROL record. The VNDR_ADDR_SCROL record is a child of the parent record VENDOR table.

An Effective Date message will display when a record is selected. All data will be retrieved, including addresses with the most recent effective dates prior to the current date.

When a record is selected, the system assigns an alias name to the record. When using joins, the alias name determines the order of the joins. In this example, the VENDOR table is given the alias "A" because it is the first record used.

The "B" alias has been assigned for the VENDOR_ADDR record. The system will first retrieve data for the "A" record and then the data for the "B" record.

Select the following fields from the VENDOR_ADDR record:

ADDRESS1, CITY, STATE and

POSTAL.

The Fields tab displays all the records and fields that are part of the current query.

Click the Run tab to run and test your Hierarchy Join.

Enter the name of your query. In this example we named the query HIERARCHY_JOIN, you may what to add a Query Definition, such as TEST FOR CLASS.

Your query results display.

Hierarchy Joins

This completes Hierarchy Joins.

You will first search for the parent table/record.

Click the Main Menu button.

Click the Reporting Tools menu.

Click the Query menu.

Enter the desired information into the Description field. Enter

a valid value

e.g.

"VENDOR"

Click the Search button.

Click the VENDOR - Supplier Header Table Add Record link.

Click the VENDOR_ID Select option.

Click the VENDOR_STATUS - Supplier Status Select option.

Click the Hierarchy Join link.

Click the VENDOR_ADDR - Supplier Address link.

Click the OK button.

Click the Query Manager menu.

Click the Create New Query link.

Click the Fields tab.

Click the Save As link.

Click the OK button.

Click the NAME1 - Supplier Name Select option.

Click the ADDRESS1 - Address Line 1 Select option.

Click the CITY - City Select option.

Click the STATE - State Select option.

Click the POSTAL - Postal Code Select option.

Enter the desired information into the Query field. Enter

a valid value

e.g.

"HIERARCHY_JOIN"

Enter the desired information into the Description field. Enter

a valid value

e.g.

"TEST FOR CLASS"