• Personal tools
  • Log in
• Navigation
  • Main Page
  • Resources
  • Recent changes
  • Random page
• Toolbox
  • What links here
  • Related changes
  • Special pages

Relations And Joins

From GTwM

agileBase automatically adds in the most important joins to get you started when creating a report, i.e. wherever there's a relation field pointing at another table, a join is added. Also, in the 'joins' section of the 'report fields' tab, there are links to automate creation of joins to other tables.

However, should you need to customise things a bit more, read on...

Understanding Relations

Every table in agileBase has a primary key field as its first field. This is an integer field that automatically increments for each new record, (starting with 1 for the first record). The value of a primary key field uniquely identifies a record within a table. By default, this value is not displayed in Panes 2 or 3 but can be seen to exist by looking at a table’s ‘fields’ tab in Pane 3.

Using primary keys, we can relate the data in one table to that stored in another by means of a ‘Relation’ field. The ‘Relation’ field creates what is known as a ‘foreign key’ by storing the primary key value of the record from the related table.

As an example, employees can be related to their employers by storing the employer primary key value within a Relation field in the employee record. As can be seen in the table below, the employee Sergey Brin is related to the employer Google by storing id 2 in the Employer relation field.

Joins

When you create a report in agileBase you immediately have access to all the fields of the parent table. However, when you need to bring together data from various tables or reports, (for example, you may want to view a report that lists details of employers and their employees,) you first need to 'join' to the related tables/reports.

To add a join to a report select the 'Joins' tab of Pane 3 whilst viewing a report.

You can only join from a tables/reports already included within the report, (this includes the parent table and any previously joined tables or reports). As can be seen in the image above, the relation field will be listed by table name and display name, (in our example this is 'Organisations: organisation name').

The 'Join Type' column allows you to specify how records should be returned when there is unrelated data in either table. The options are:

1. inner: Only returns records where the join fields have matching values in both tables/reports.
2. left outer: Returns all records from the left table. Records from the right table will only be returned if they match with left table records on the join fields. Where a record from each table matches on the join fields, they will be returned as one row.
3. right outer: Returns all records from the right table. Records from the left table will only be returned if they match with right table records on the join fields. Where a record from each table matches on the join fields, they will be returned as one row. This is obviously the reverse of a left outer join.
4. full outer: Returns all records from both tables. Where a record from each table matches on the join fields, they will be returned as one row.
5. none: This join type is equivalent to a 'cross outer' or 'cartesian product' in SQL. It is unlikely that you will need to use this join type.