Like what you see? Have a play with our trial version.

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migration of unmigrated content due to installation of a new plugin

Anchor
top
top

Table of Contents
classcontents

Overview

Styleclass
ClasstopLink

top

Once you have dragged more than one table onto your canvas, you need to create joins between related tables. Joins are critical to the relationship diagram as they allow the meaningful combination of data from multiple tables.

What is a join?

Styleclass
ClasstopLink

top

A join is a clause that combines rows from two or more tables. Joins are used to ensure that queries containing data from multiple tables do not return an incorrect result. The tables usually have a parent-child relationship.

Note: With the Yellowfin you cannot create loop joins. A loop join is where a table is joined to multiple tables that join back on themselves creating a closed loop.

Join Types

Inner Joins

 

An inner join (sometimes called a "simple join") is a join of two or more tables that returns only those rows that satisfy the join condition.

Outer Joins

 

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
The outer join is specified by selecting the appropriate ‘From Table’ as the starting point for the join builder. The outer table contains the column for which you want to return all values, even if they are unmatched.
Note: The view builder has the limitation of not being able to have an INNER JOIN on the discretionary end of an OUTER JOIN. If you have a join error whilst creating an outer join you can change the direction of the join creation.
For example:
So if you are currently creating an outer join from Table A to Table B, go to Table B and create the outer join back to Table A instead.

Cross Join

 

A cross join (sometimes called a Cartesian join) is a join that will include all rows from one table with all rows from the other table. No matching will be applied.

Creating and editing a Join

Styleclass
ClasstopLink

top

  1. To create a new join click on the Join icon on the table you wish to include in the join.

    This will open the join popup.
  2. Select the join type and the table you wish to join to
    Note: you can also use the detect join option if the tables have key fields with the same name,
  3. Define the Join Logic by either:
    1. Select the ‘From Column’, the operator and the ‘To Column’ that you want to be included in the join. Click Add to add the column relationships to your join.
      Note: Multiple join columns can be added into the join definition. For more detail on joins details see the conditions section below.
    2. Choose the 'Detect Joins' option. This performs a text match search on the names of columns in each table. When it finds columns in both tables with identical names it adds them to the join. This does not look at any underlying join logic built into your database.
  4. Click Save and Close to save your join and return to the view canvas
  5. The tables will now have a join displayed as a line between them.

Deleting Joins

Styleclass
ClasstopLink

top

If you want to delete an existing join without deleting the associated tables

  1. Click on the Join icon
  2. Click the delete join link on the popup and the join will be deleted.

 

horizontalrule
Styleclass
ClasstopLink

top