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:

Customer Table
Account_No*	Title	Income	Gender
111	Vice President	$100,000+	Female
222	Manager	$40,000 - $59,999	Male
333	Director	$80,000 - $99,999	Male
444	Analyst	$20,000 - $39,999	Female

Solicitation Preference Table
Account_No*	Solicitation_Type	Preference
111	Telemarketing	No
111	Direct Mail	Yes
222	Email	No
333	Telemarketing	No

Orders Table
Account_No*	Order_No*	Order_Date	Product_No	Quantity	Order_Amt
111	123	12/3/2015	A101	3	$150.00
111	456	4/18/2015	A202	1	$15.99
222	789	3/31/2016	A303	5	$75.00

Product Lookup Table
Product_No*	Product_Category	Manufacturer_No	Unit_Cost	Prod_Desc
A101	Housewares	12345678	$50.00	Floor Lamp
A202	Electronics	98765432	$15.99	TV Remote
A303	Kitchen	88997766	$15.00	Mixing Bowl

Address Table
Account_No*	Address_Line1	Address_Line2	City	State	Zip_Code
111	999 Maple Street	t. 5A	Lanham	MD	20706
222	4405 Church Ave.		Harrisburg	PA	19776
333	1809 21st Street	Apt. 108	Philadelphia	PA	19007
555	48 Hancock Blvd.		Jaffrey	NH	02779

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.

Join of Address and Orders Tables
Account_No	State	Order_Date	Product_No
111	MD	12/3/2015	A101
222	PA	4/18/2015	A303

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)

Warning

This section is intended to provide information regarding many-to-many joins so that you can understand what they are and be able to avoid them and the complications associated with them.

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.

To Create Joins

Drag and drop a database from the library to the main window
To create a new join, drag and drop a second database from the library to the main window
Repeat step 2 to select another table to join. The added table will appear with all the fields in the table displayed. You can click and drag the title bars of the added tables to rearrange them in the Join Builder dialog.
If necessary, click to check the fields in each table that you want to appear in the join. By default, the All Fields option is checked, meaning all fields from the table appear in the table join.
To join the tables, click and drag the field you wish to join (i.e. the common field where you want the values to match) from one table to the same field (or related field) in the other table.
A line appears connecting the tables based on the common field you selected, as shown in the Join Builder picture. (Join Builder creates an inner join by default. See "Advanced Join Features" later in this manual for information on creating different types of joins.
Repeat steps 4-6 for each table needed in the join.
Click publish ( ) to save the join.

Note

An inner join is the default type of join in Join Builder. See "Advanced Join Features" later in this manual for information on creating different types of joins.
In MarketWide's SQLManager, all joins display in a System Objects folder. The System Objects folder is generated automatically the first time you create a table join. If you eventually drop all joins, the System Objects folder remains in view, although it is empty.
Join fields can have different names, but must contain the same type of data. For example, suppose the ACCOUNT_NO field in the Customer table in Figure 1 had the name CUSTOMER_NO instead. You could join the Customer table to the Orders table by joining CUSTOMER_NO to ACCOUNT_NO because the fields contain the same data.

To Edit Joins

Right-click over the join and select Edit.
Make any necessary changes by right clicking the threads that connect the tables and choosing Properties. This will open the Join Properties dialog. For more information on the Join Properties dialog, please see "Advanced Join Features" later in this document.
Click publish ( ) to save the join.
Click Publish As under the drop-down arrow next to publish if you wish to save the edit as a new join.

Note

The SQL query pane allows you to view the SQL used to create the join.

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.

Note

Common fields in join tables can have different names, but must contain the same type of data. For example, suppose the ACCOUNT_NO field in the Customer table had the name CUSTOMER_NO instead. You could join the Customer table to the Orders table by joining CUSTOMER_NO to ACCOUNT_NO because the fields contain the same data.

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.

Option	Description
Left Object	The name of the table on the left is displayed below.
Right Object	The name of the table on the right is displayed below.
Join Expression	The SQL expression for the join. Allows you to change the condition on which the two tables are joined.

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.

Join Type	Link Properties Rows Options
Inner Join (Default)
Left Outer Join
Right Outer Join
Full Outer Join

To Change Join Types

In the Join Builder dialog, right click the join line between two tables and select the Properties option.
Click to check whether to select all rows from one or both tables in the join.
Choose OK to confirm your changes in the Link Properties dialog.
In the Join Builder dialog, choose File > Save from the menu or click the Save toolbar button to save the changes to your join. Go to File > Exit to exit the Join Builder dialog. For users, the edited table joins will be displayed in MarketWide's SQLManager, in a folder named System Objects.

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

Account_No*	Title	Income	Gender	Deceased_Flag
111	Vice President	$100,000+	Female	N
222	Manager	$40,000 - $59,999	Male	Y
333	Director	$80,000 - $99,999	Male	N
444	Analyst	$20,000 - $39,999	Female	N

Orders Table

Account_No*	Order_No*	Order_Date	Product_No	Quantity	Order_Amt
111	123	12/3/1999	A101	3	$150.00
111	456	4/18/2000	A202	1	$15.99
222	789	3/31/1999	A303	5	$75.00
333	988	12/15/2000	A404	2	$55.99
444	999	3/18/2000	A505	1	$99.99
444	111	8/9/2000	A606	3	$22.99

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

Browser not supported