Report Table Joins

In the Report Desginer, the list of tables is shown on the left side of the screen. To use fields from more than just the master table you have selected, you will need to join to other tables.

To join to a table:

1. Right-click the table. You will see three join options:

   • Left Join

     If you are unsure which join to use, this will work for the vast majority of situations.

     A left join will include all data from the first table, even if there is no corresponding data in the second table. The following example shows two tables and the results you would get from joining from table 1 to table 2:

     

     Note that John Smith is included because he has a row in table 1, even though he has no table 2. Learner 004 is not included because there is no 004 record in table 1.

   • Inner Join

     An inner join will only join data where there are results on both sides. Using the same table examples above, an inner join would produce the following:

     

     Note that neither John Smith nor learner 004 is included in this join, as they must have data in both tables. John Smith has no row in table 2 and learner 004 has no row in table 1.

   • Right Join

     A right join will include all data from the second table, even if there is no corresponding data in the first table:

     

     Note that John Smith is not included, as he has no row in table 2, but learner 004 is included as there is a 004 record in table 2, even though there is no 004 record in table 1.

     See Which join to use? for more information.

2. Select the appropriate join. Once locked, the icon will appear next to the table.
3. If you need to unlock a table, right-click the table and select Unlock.
4. You expand tables to see what other tables can be joined to them. To do this, either double-click the table or click the arrow next to the table.
5. You can also show the table names as they are called in the database (as opposed to their display names) by clicking the button above the table joins section.
6. If you need to apply a custom filter to a join, right-click the table and select Custom filter.

Which join to use?

For most reports, a left join will suffice as there are few likely situations where this will not work, although it may not be the most efficient option. If you are using a left join and report performance is slow, consider whether an inner join would work better.

An inner join will generally be more efficient and make reports load faster, but be careful that data is not being unintentionally excluded. As an example, using an inner join between Trainee Basic Details and Trainee Period of Training is a good idea, as every learner has a row in both of these tables. Using an inner join between Trainee Basic Details and Trainee Visits, however, would only show learners who have a visit recorded against them. This could be good or bad, depending on what you wish to show in the report.

Right joins can be useful for certain circumstances, but usually a left join is preferable.