Reporting Across Data Sources: Types of Joins

It is critical for publishers to have access to performance metrics to make informed, evidence-based editorial decisions. Aries’ workflow management solutions, Editorial Manager® (EM) and ProduXion Manager® (PM), come equipped with a robust Enterprise Analytics Reporting (EAR) suite that allows publishers to quickly surface key publication data. Within EAR, data is stored across dozens of tables and views. To easily report across data sources against one or more common fields, publications can use EAR’s “Join” function.

There are five types of Joins that can be used depending on the desired results:

  • Left Join: Combines data source 1 and the intersecting data from source 2
  • Right Join: Combines data source 2 and the intersecting data from source 1
  • Inner Join: Results in only data that applies to both sources
  • Full Join: Results where there is a match(es) on the field(s) joined in either one of the data sources
  • Cross Join: Combines each record of the first data source with each record of the second, but does not need a common field

 

The two most popular types of Joins used by publishers in EM/PM are Left and Inner Joins. To help publishers use these two Joins types in practice within the system, we have broken down the concept into a relatable, real-world analogy below. For more information on Joins and example tutorials in EM, watch our overview video on Joins or check out this in-depth webinar. For support, please contact your Aries Account Coordinator.

 

UNDERSTANDING JOINS: ORDERING COFFEE EXAMPLE

Brianna is ordering four coffees for herself and her coworkers. Information regarding the four coffees she has ordered are stored in multiple datasets. Dataset 1 describes the type of coffee, dataset 2 describes how much espresso and milk each coffee has, and dataset 3 describes the flavor shots put in each coffee.

*select all columns

 

INNER JOIN

SELECT * from COFFEE INNER JOIN DETAILS ON COFFEE.ID = DETAILS.COFFEE_ID

Here is an Inner Join for all data within the COFFEE and DETAILS tables. Note: The duplicate column for coffee_id displays because it is included in both tables and all columns are selected.

SELECT type, espresso, milk from COFFEE INNER JOIN DETAILS ON COFFEE.ID = DETAILS.COFFEE_ID

With this Inner Join, the resulting table displays information on the type of coffee, and the amount of espresso and milk in each cup. There is no duplicate column with the coffee_id as seen in the previous table.

SELECT * from COFFEE INNER JOIN DETAILS ON COFFEE.ID = DETAILS.COFFEE_ID INNER JOIN FLAVORS ON COFFEE.ID = FLAVORS.COFFEE_ID

This type of Inner Join displays data from all three tables, but excludes the cortado since it does not have any added flavor shots.

 

LEFT JOIN

SELECT * from COFFEE INNER JOIN DETAILS ON COFFEE.ID = DETAILS.COFFEE_ID LEFT JOIN FLAVORS ON COFFEE.ID = FLAVORS.COFFEE_ID

This Left Join would combine all three data sets and include the cortado.