Table Joins

Normally to create a fully functioning Dashboard we will need to reference more than one table/view and the table join(s) will need to be defined in the dashboard.

The screenshot below shows several different tables and the joins, indicated by the blue lines, between them.

Creating a Join

To create a join, click onto the header of the table we want to join from and click on the ‘Joins’ tab, this time however click on the ‘Add’ icon to create a new join.

You will then be presented with a list of the tables/ views already configured in this data connection. Click the one you want to join to and the line for this join is created.

Clicking on the newly created line at the top of the screen will display the columns in each table.

At this stage, you could manually type in the join in the ‘result’ window. Normally however, even if the join is more complex than a simple ‘equal to’, it is better to select the field(s) from each table using the two lists on the left-hand side, which will ensure that the correct aliases and object quotes are pulled through. After selecting the two fields, click the ‘Append to SQL’ button.

The join will be created and displayed in the Result area.

For more complicated joins you can click on multiple objects before pressing the ‘Append to SQL’ button. You can also manually edit the SQL generated, any SQL syntax that is valid for your database type such as hints, substrings, cast etc. can be used.

Testing a Join

After the join has been built it can be tested using the ‘Test join SQL’ icon in the Result area which will return sample data from both tables.

Clicking the ‘SQL’ tab will display the SQL executed to get this result set.

Deleting a Join

To delete an existing join, click on the header of a table (the grey bar at the top of the table that shows the name of the table).

This will show the following window. To view existing joins, click on the ‘Joins’ tab. In the following example, we can see that the postcode field in the ukpostcodes table is joined to the CUS_POSTCODE field in the nr_customers table.

If we then click the ‘To Delete’ button at the bottom left of the window

the screen will change, and a tick box will be displayed at the end of the join row. To delete this join, select the tick box and then press ‘Delete Selected’.

Having ‘orphaned’ tables is generally a bad idea. Where stand-alone tables are required, they should ideally be placed in a separate data connection(s) specifically for such purposes.

Please refer to the following pages for more information on Table Joins.