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.
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 next to these types of joins):
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|
||Up to 1 object||
||Up to 1 object||
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||
|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||
ImportantThere 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:
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.