Data, such as person records, fields, and mailings, are organized and stored in tables.

You can think of a table as a single page in a spreadsheet, with each data object taking up a row. A collection of these tables form a Relational Database, like Slate. A join allows you combine data from two (or more) tables based on a related column they share, like record_id. For example, you can join a Person table to the Applications table to see all the application records a person may have.

Relationships between tables

Relationships between the tables in a Slate database are established in one of two ways: one-to-one relationships, or one-to-many relationships.

• One-to-one: An object on one table relates to only one object on another table. For instance, any application record can only be related to one person record.

• One-to-many: An object on one table can relate to one or more objects on another table. For instance, a person record in the Person table can be related to multiple application records in the Application table.

Joins

The mechanism by which you create these relationships is called a join. Joins increase the flexibility and usefulness of queries by combining data from multiple tables; without joins, you could only query on data from one table at a time.

In the below example, we can see a one-to-one join called Device by Type, Rank and a one-to-many join called Devices. Notice the muted one-to-many icon  next to one-to-many joins.

Understanding these relationships is integral to building a successful query. To learn more about object relations in Slate or joins in Configurable Joins, use the Configurable Joins Base Explorer tool in the Database section of your database.

📖 Slate Database Structure

Types of Joins

There are three types of joins:

1) One-to-One

Returns up to one object.

Useful for:

• Retrieving a single object associated with a record, like the corresponding person record for an application.

2) One-to-Many

Can return more than one object.

Useful for:

• Retrieving several objects associated with a record, like all event registrations for a record.

3) Subquery Join

Returns up to one object.

Useful for:

• Retrieving one of many objects associated with a record that is not normally ranked by Slate, such as returning the next upcoming event registration for a record

• Using an independent subquery to join objects that are not explicitly related.

Locations to Add a Join

There are two places to perform a join within a query:

1) At the main level

These are joins added at the base of your main query/level. These joins will only ever return up to one object, which is why you will almost never want to make a one-to-many join at the main level.

Useful for:

• Filtering for records who have a rank 1 overall address in the United States and exporting the address information using the "Address by Rank Overall" join

• Exporting information about a records rank 1 high school using the "School by Level of Study, Rank" join

2) As a subquery export or subquery filter

These are joins added inside of a subquery export or subquery filter. Subquery exports and filters allow for one-to-many joins to return more than 1 object

Useful for:

• Exporting a comma-delimited list of phone numbers for a record

• Counting the number of applications related to a person record

• Summing the lifetime giving for a person record

⭐ Best Practice

• You very rarely need to make a one-to-many join at the main level of a query.

• For instance, if you wanted to exclude records by making a main join from Person to Tags, Slate would randomly retrieve one tag set for that record. That tag may or may not be the Test Record tag.

• Instead, use a subquery filter that joins from Person to Tags so we can look at all tags associated with a person record, like so:

  

📖 Further reading

• Subquery Exports

• Subquery Filter

Types & Locations

Combining the above two sections, we can see the relationship between a join's location and its type:

Deciding what path to take ultimately depends on what you're looking to accomplish with your query.