Queries perform various functions in Slate, such as returning and displaying specific data, creating recipient mailing lists, and building reports in Report Builder. Configurable joins enhance the capabilities of data extraction. A join combines data from multiple tables based on relationships between them and enables you to specify how the related table should be joined to the query base. This increases the flexibility in query building and expands access to the database tables.
In Slate, data about various objects, such as person records, fields, and mailings, are organized and stored in tables, with multiple tables forming a relational database. Relationships between the tables in a Slate database are established through a one-to-many relationship.
Slate databases are collections of tables that are interrelated and store data. The objects often have relationships with each other, such as a one-to-one or one-to-many relationship. The Query Builder provides access to the underlying tables and enables data extraction and manipulation. Some of these query populations present the data in a predetermined manner, such as the Prospects query population, which excludes person records with an Opt-Out tag.
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, each application record can only be related 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, one person record in the Person table can be related to multiple application records in the Application table
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.