﻿ Joins

# Joins

Pyramid determines the table relationships (joins) based on heuristics that define the first column in each table as the primary key column. It then finds the primary key column in each table, and looks for tables that have the same column as a foreign key. The algorithm used to create joins can be changed or disabled if needed.

## Join Types

• Full Outer Join: returns all rows from both tables
• Inner Join: returns all rows from both tables based on a matching column. Only returns rows where the matching column's values are identical.
• Left Outer Join: returns all rows from the first/ left table, and matching rows from the second/ right table.
• Right Outer Join: returns all rows from the second/ right table, and matching rows from the first/ left table.

## How Joins are Defined

Pyramid uses heuristics to determine the table relationships (joins) in the data model. There are three different algorithms (called 'auto-relationships') from which to choose; the default algorithm defines the first column in each table as the primary key column. It then finds the primary key column in each table, and looks for tables that have the same column as a foreign key. Joins are created from the tables containing the primary key column, to any tables containing that same column as a foreign key.

If the default algorithm doesn't suit your data model, you can choose a different auto-relationship to create joins based on table or column names. Alternatively, you can disable auto-relationships and add all the joins manually.

Note: by definition, primary key columns contain unique values; each row of a primary key columns contains a unique identifier. If a column that was set as a primary key contains any duplicate rows, you will receive a warning after the model is processed

New joins can be added from a given column in a table, to a selected column in another table. Joins can be edited from the Join context menus, and from a selected join's Properties panel.

Editing joins can involve changing the join type, changing or adding join key columns, setting a join key condition, changing the join key operation, the join direction, or making a join bidirectional.

### Editing Relationships

Table relationships can be edited by changing existing joins, adding new joins, or deleting joins.

#### Change Join Type

Change the join type of an existing join by clicking on it. This will open both the join type context menu (red highlight below), and the Properties panel (green highlight). You can change the join type from either location.

Add a join by right clicking on the column belonging to the owner side. From Add Relationship, select the table and column to which you want to the join to connect.

#### Delete Joins

Right click on the join and click 'Delete' from the context menu (red highlight below).

#### Change Join Direction

The join direction can be changed for any join, both from the join context menu (red highlight below), or from the join's Properties panel (left click on the join to open its Properties panel).

#### Validate Join

Ensure that the join relationship is valid. This can be done from the:

• Join context menu (red highlight below).
• Join Properties panel (left click on the join to open its Properties panel).
• From the ribbon (select the preview size and validate all joins in the model for the selected number of rows; invalid joins will be shown in yellow in the join diagram).

#### Copy Join

Click Copy to copy the script that underlies this join to your clipboard. Tip: You can use this script when testing in the database.

### Join Properties

#### Join Type

Change the join type from the 'Join Type' drop-down list (green highlight below).

#### Bidirectional

Joins can be one-way or two-way (bidirectional). The most common type is one-way, and this is the default in Pyramid. You can make a bidirectional join by enabling the 'Bidirectional' option from the join's Properties (red highlight below).

#### Many to Many

When the relationship between the primary and foreign tables is "one-to-many" and doesn't validate due to duplications of unique keys it will replace the relationship to a many-to-many relation and as a result will be a valid connection (the user should understand his data and understand that it can be a many to many relationships).

#### Add or Edit Join Keys

• Add join keys by clicking 'New Join Key' (blue highlight below) and selecting the required columns.
• Change the join key columns by selecting different columns from the drop-downs (purple highlight below).
• Insert an And/ Or condition between multiple join keys.
• Change the join direction by clicking the double arrows icon (yellow highlight below).
• Change the join key operator (white arrow below).