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.

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.

📖 Further reading

• Slate Database Structure

• 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.

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.

To create a one-to-one join:

1. Click the join button.

   

2. Select a one-to-one join.

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.

To create a one-to-many join:

1. Click the join button.

   

2. Select a one-to-many join.

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.

To create a subquery join:

1. Click the subquery button next to the join button .

   

2. Add the requisite join(s).

3. Add filters and/or sorts.

4. Select an overall base.

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 bottom 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.