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:
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 | 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