Joining

Slate stores data about different objects—such as person records, fields, and mailings—in distinct tables. Those objects often relate to other objects, like a person record having multiple school records. Joining is the process of connecting one object to another object, of connecting rows in one table to a row (or rows) in another table; Joining allows you to query on information outside of your starting query base.

Object Relations

Objects in Slate most commonly relate to one another in one of two ways:

  • One-to-one - This is a relationship where an object on one table can relate to only one object on another table. For instance, an application record can only relate to one person record
  • One-to-many - This is a relationship where an object on one table can relate to one or more objects on another table. For instance, a person record can relate to multiple application records.

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" (you'll also notice a muted one-to-many icon download.png next to these types of joins):

mceclip0.png

Appreciating these relations is integral to building a successful query. To learn more about object relations in Slate or joins in Configurable Joins, consider exploring the Configurable Joins Base Explorer tool inside of Database.

Types of Joins

There are three types of joins using Configurable Joins:

Join Type How Can Return Use Case
One-to-One
  1. Clicking the download__1_.png join button
    mceclip1.png
  2. Selecting a one-to-one join
Up to 1 object
  • Retrieving a single object associated with a record, like the corresponding person record for an application
One-to-Many
  1. Clicking the download__1_.png join button
    mceclip1.png
  2. Selecting a one-to-many join

Depends

 

  • Retrieving several objects associated with a record, like all event registrations for a record
Subquery Join
  1. Clicking the subquery button download.png next to the join button download__1_.png
    mceclip2.png
  2. Adding the requisite join(s)
  3. Adding filters and/or sorts
  4. Selecting an overall base
Up to 1 object
  • 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:

Location Added Description Use Case
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
  • 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
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
  • 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

Important

There is almost never a reason 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. In that case, we would use a subquery filter that joins from "Person" to "Tags" so we can look at all tags associated with a person record, like so:
mceclip3.png

Types & Locations

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

Join Type Location Added Returns
One-to-one Main Level Up to 1 object
One-to-many Main Level Up to 1 object
Subquery Join Main Level Up to 1 object
One-to-one Subquery Export or Subquery Filter Up to 1 object
One-to-many Subquery Export or Subquery Filter Up to N objects
Subquery Join Subquery Export or Subquery Filter Up to 1 object

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

Was this article helpful?
2 out of 3 found this helpful