Overview

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?

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

JoinDescription

Inner

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


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


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

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

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.

 

top