JoinBuilder

The JoinBuilder allows you to create table joins for use by all MarketWide users. Any time you join tables, the following conditions must exist:

• Tables to be joined must share a common field – To join tables, you need a field that exists in both tables and lets you match up their records.
• The common field must also be a key field in at least one of the tables to be joined – A key is a field or set of fields that uniquely identifies a record in a table. To join tables, the common field mentioned above must be the key for at least one of the tables you wish to join.

Topics

Joining Tables

Joining tables provides one method for querying data from multiple tables by combining their fields into one consolidated table. Why would you ever need to query against more than one table? Consider the tables in Figure 1 below:

Figure 1: Example of querying against multiple tables 

Suppose you need to run a query to generate a list of all customers in Pennsylvania who purchased product A303 during the year 2015. You need information from both the Address and Orders tables to compile that list. Joining these tables combines the necessary fields into one table, which you can select to run your query against. Figure 2 below illustrates one possible join of the Address and Orders tables. There are other possible results of joining these tables covered in "Types of Table Joins" later in this manual.

Figure 2: Possible join of address and orders tables

Types of Table Joins

So far, we have discussed why you may need to join tables, as well as the conditions required to join them. Before we jump into the steps for performing joins, we need to discuss the various types of table joins. 

Inner Joins
An inner join consists of records where common field values match in all tables to be joined. For example, an inner join of the Customer and Address tables would contain records for account numbers appearing in both tables. 

Outer Joins
Like inner joins, outer joins contain records where common fields match in all tables. However, they additionally contain all the non-matched records from one or more of the tables. For example, suppose you want to join the Customer and Address tables. There are three possible outer joins, as shown below:

• • Left Outer Join: You can join the Customer and Address tables and include all the Customer table records, even if there's no matching account number in the Address table.
  • Right Outer Join: You can join Customer and Address so that all the Address table records appear, even if there's no matching account number in the Customer table.
  • Full Outer Join: Finally, you can join the Customer and Address tables and include all records from both tables. 

One-to-One Joins
So far, we have discussed inner and outer joins, which define which rows end up in the resulting join. For simplicity, the examples in previous sections all involved one-to-one relationships between the tables to be joined. A one-to-one relationship occurs when there is only one record per key in each table. For example, any join of the Customer and Address tables in the scenario given above is one-to-one because there is only one row per account number in each table. In other words, each account number in the Customer table can have only one match in the Address table, and each account number in the Address table can have only one match in the Customer table.

While one-to-one joins are simple, they are also rare. The next few sections discuss one-to-many joins, which occur much more frequently when dealing with complex databases, and many-to-many joins. 

One-to-Many Joins
In a one-to-many join, one table has one record per join field and the other has many records per join field. In other words, each record in the first table can have many matching records in the second table, while the second table only has one match per record in the first table. For example, suppose you want to create an inner join of the Customer and Orders tables. The Customer table has one record per account number, while the Orders per customer. By definition, an inner join of these tables contains only those account numbers that are in both tables. However, since the Orders table has multiple records per account number, the data from the Customer table must repeat for each record in the Orders table in the final join. 

Many-to-Many Joins (Cartesian Joins)

A many-to-many relationship exists between tables with multiple records per join field. Each record in the first table can have multiple matches in the second table, and vice versa. The Orders table and Solicitation Preference table may, for example be a many-to-many relationship since there are multiple records per account number in both tables. Suppose you try to build an inner join of these two tables, including only records for account numbers that appear in both tables. For each row in the Orders table, we need the data from each matching row in the Solicitation Preference table. Specifically, account number 111 will appear four times in the join!

For each of the two records for account number 111 in the Orders table, there are two matching rows in the Solicitation Preference table, resulting in four rows in the join. This is an example of how many-to-many joins can result in inaccurate database queries.

Creating Joins

The Join Builder allows you to select tables to join, to indicate which field to join on, and designate what type of join to create. These topics are discussed in the following sections.

Conditions for Joining Tables

Now that you know why you might need to join tables, you must understand the circumstances under which it's possible to join tables. To join tables, the following conditions must exist:

• • The tables to be joined must share a common field – To join tables, you need a field that exists in both tables and lets you match up their records. For example, you can join the Customer table and the Orders table because both contain the ACCOUNT_NO field. In other words, you can join these tables by matching up account numbers from each table and combining the data.
    
    
  • The common field must also be a key field in at least one of the tables to be joined – First, a key is a field or set of fields that uniquely identifies a record in a table. Table keys are designated by an asterisk ( * ). So, the ACCOUNT_NO field is the key for the Customer table, meaning it's the field that uniquely identifies each customer. In other words, there is one, and only one, customer with ACCOUNT_NO 111. Since customers may place multiple orders, both the ACCOUNT_NO and ORDER_NO fields comprise the key for the Orders table. In other words, there may be more than one record in the Orders table with ACCOUNT_NO 111, but there's only one record with ACCOUNT_NO 111 and ORDER_NO 123. You can join the Orders and Product Lookup tables from Figure 1 because they have a common field in PRODUCT_NO, and the common field PRODUCT_NO is the key field in the Product Lookup table.

Advanced Join Properties

The previous section discussed the Join Builder's default table join options, specifically a simple inner join created by clicking and dragging the join fields between two or more tables. In addition to inner joins, you can create outer joins and apply filter criteria to the tables to be joined, all using the Join Properties dialog shown below.

The Join Properties Dialog Box

To access the Link Properties dialog, highlight the join line between two tables in Join Builder and choose Properties from the menu. 

Changing Join Types

By default, the Join Builder creates an inner join when you click and drag join fields between tables. In Types of Table Joins earlier in this manual, you learned about inner joins and right, left, and full outer joins. The Link Properties dialog lets you create these types of joins. Not every database engine supports full outer joins. Check with your Database Administrator to see if your database allows them.

Adding Filter Criteria
Another advanced feature available in the Join Builder is the ability to add filter criteria to table joins. To add a filter, click on the Filter icon on the Join Builder toolbar.

For example, suppose you want to join the Customer and Orders tables shown below, but only include customers who are not deceased. 

Customer Table

Orders Table

If you add a Filter that says Customer.Deceased_Flag = N, then Customer_No 222 will not appear in your join.