An Ultorg perspective can combine data from multiple data tables, based on common identifiers such as email adresses or customer IDs. This operation, as previously discussed, is known as a join. There are two ways to add a new join to an existing perspective:

• Automatically, by picking a suggested join () in the field selector.
• Manually, using the Custom Join action, which we discuss here.

Automatically suggested joins are based on foreign key relationships in the data source, where relevant, as well as relationships previously added through the Custom Join action.

These operations, like others discussed in this chapter, change only how you see your data. They do not change the data itself.

How to Use

From an open perspective, open the context menu for the field you want to join on, and click Custom Join. Then, select the Child Table you would like to join with, and the field in the child table that you would like to match against. This is the join condition. Click OK.

For example, we may wish to find log events relating to email addresses in a Users table:

Here, we started from the Users table, in a single-table perspective, and then joined against a different table called Log Events. Rows in the two tables are matched based on the Email and Param 1 columns, respectively. The resulting perspective might look as follows:

The join appears as a new subquery, joined against its parent. In this example, the new Log Events subquery can have multiple matching rows per parent row in the Users subquery.

By default, a parent row will remain even when there are no matching child rows. This is seen e.g. for the third user in the example. The Hide Parent if Empty setting controls this behavior.

After invoking the Custom Join action, it is often useful to open the field selector in the new subquery to adjust visible fields. Ultorg will guess some reasonable defaults to start with.

The field selector will show join conditions in grey, indicated by the join icon ():

If you get the join conditions wrong, press Undo () and try again.

Once a join works correctly, it is seldom useful to change the join conditions. Still, existing join conditions can be modified from the Fields sidebar (seen above), by clicking the property selection button () and showing Joined On as an editable column.

Text matching is case-sensitive. So e.g. “John” will not match “john”. For case-insensitive matching, you can join on a formula with the lower function on both sides.

Joining on Multiple Fields

A join can match multiple pairs of fields between the base table and child table. To add a new pair of fields to match, press the plus button () in the Custom Join dialog. Or, you can use multiple selection to select fields on each side of the join.

For example, you might include both Organization Name and State when attempting to look up specific financial institutions across two datasets (example from this demo):

All pairs of fields must match for the overall row to be considered a match.

Joining on a Formula

You can join on either table columns () or formulas in calculated fields (). Joining on a formula is useful when an identifier must be extracted or manipulated in some way before it can be matched with values in another table.

For example, we might use the jsonValue function to extract a “subscription” field from a JSON column, and then join on the formula against the ID column of a Subscriptions table.

If you need a formula on the child side of a join condition, prepare the child table first as a separate perspective, and save it. Then use the saved perspective () as the Child Table in the Custom Join dialog. This copies the saved fields and subqueries into the current subquery.

For example, you could have a formula that extracts domains from email addresses in the “Users” table, save this perspective, and then join it against itself to list all users and, for each user, all other users with email addresses on the same domain:

This also happens to be an example of a self-join, where a table is joined against itself.

Mixing Data Types

The fields that are being matched in a join condition must be of the same data type. You can use a formula to perform conversions if necessary; see above.

When joining a field of type Number, UUID, or Other against a Text field, the Custom Join action will automatically insert a formula to convert values to text for matching purposes. In other cases, mismatched data types will yield an error message.

Other Join Conditions

The construction known as a join in Ultorg is always based on exact comparisons between pairs of values. Such joins are called “equi-joins” in the database literature. If you need to express other kinds of join conditions, you can put them in a formula and filter on the formula.

When to Use

The Custom Join action is commonly needed for data sources that do not declare explicit foreign key relationships between tables, or when combining data from different data sources.

Declaring a Relationship

You only need to invoke the Custom Join action once per table/column combination. Ultorg will remember the join for future perspectives you might create, and suggest it in the field selector.

For example, the Custom Join in the first example on this page will cause Ultorg to remember a relationship between the Users and Log Events tables on the “Email” and “Param 1” fields, respectively. In new perspectives, the same join will be suggested again whenever the Fields popup is opened on a subquery associated with either table:

(In the example above, a “via” indication is shown for disambiguation purposes, because the Log Events and Users tables are also related via the User ID field.)

If you explicitly Delete (not merely hide) a subquery created or suggested by a Custom Join, the relationship will be forgotten with respect to future suggestions in the field selector.

Foreign key relationships from the data source are never forgotten.

Multiple Joins Over One Relationship

For some queries, it may be useful to include the same join relationship multiple times, in separate subqueries. The field selector suggests joins only once per relationship, but the Custom Join action can be invoked any number of times.

For example, we may wish to show Contacts who have at least one email address containing @gmail.com, but then show the complete list of email addresses for each such contact. This can be done with two instances of the EmailAddresses table, each in their own subquery:

To produce the perspective above, we would use Custom Join twice on the ID field in Contacts, and join against the relevant foreign key in EmailAddresses each time (e.g. “ContactID”).

Alternatively, we could start the perspective from the EmailAddresses table, filter it, then join on Contacts, and then join again on EmailAddresses to get an unfiltered instance of the latter.

The Move to Root action can be used to switch between the latter two constructions.

At the SQL level, the above examples are analogous to a query with multiple instances of the same data table, i.e. the EmailAddresses table having two FROM clause entries.

Another use is to traverse a many-to-many relationship over the same table twice. In the Course Catalog demo database, we can find instructors and the courses they teach, as well as the full list of instructors in each of those courses:

In this case we would start building the perspective from the Instructors, then expand to Courses using suggested joins from foreign key relationships, then do Custom Join once to join back to Sections, and then finally contine expanding back to Instructors using suggested joins.