If you are adding more than one business object to a query, you must add a relationship, also known as a join, to the two objects. Creating a join between the two objects tells iMIS how you want the two data sources to communicate.
Tip! Not every table will contain data for every contact. If this is the case for the tables you are joining, be sure to use the Left Join or Right Join option. Be sure to review the examples below to understand which join type is right for your situation.
Equals
Think of business objects as tables of data. With only one business object in a query, you only have one table of data.
The column header in this first table may have properties such as ID, First name, Last name, and Email.
| Business Object 1: NetContactData | |||
|---|---|---|---|
| ID | First Name | Last Name | |
| 11234 | Margo | Smith | margo@mail.com |
| 12345 | Stacey | Johnson | stacey@mail.com |
| 13456 | Tammy | Brown | tammy@mail.com |
| 48569 | David | Garcia | david@mail.com |
With only one table of data, you do not need to create a relationship, but when you add a second business object with a whole new set of data and columns, that is when you need to create the relationship. The column header in this second table may have properties such as ID, Product Code, Member Type, and Transaction Date.
| Business Object 2: CsActivity | |||
|---|---|---|---|
| ID | Product Code | Member Type | Transaction Date |
| 11234 | DUES_FEE | STU | 06/31/2021 |
| 12345 | CC_AUTH | GRAD | 06/31/2021 |
| 48569 | DUES_FEE | REG | 06/31/2021 |
With the addition of this second table, iMIS now needs to understand how these two tables are supposed to communicate. Typically, we will want to join on the ID property.
When you join on the ID property, you are telling iMIS to select all rows from both tables as long as there is a match between the columns. If there are rows in the first table that do not have matches in the second table, these orders will not be shown. The Equals relationship will only return records that have matching values in both tables.
In the example above, Tammy Brown does not have a matching row in the CsActivity table, so she will not be returned in the query if you set the relationship to NetContactData.ID = CsActivty.ID. In the above example, the data returned would look like the following (depending on the display columns you chose):
| NetContactData.ID EQUALS CsActivty.ID | |||
|---|---|---|---|
| ID | First Name | Last Name | Product Code |
| 11234 | Margo | Smith | DUES_FEE |
| 12345 | Stacey | Johnson | CC_AUTH |
| 48569 | David | Garcia | DUES_FEE |
Left Join
The Left Join relationship returns all records from the left table, and the matched records from the right table. If there is no match, the result is 0 records from the right side.
In this example, you are using the NetContactData business object and the CsOrders business object.
| Business Object 1: NetContactData | ||
|---|---|---|
| ID | First Name | Last Name |
| 18220 | Aaron | Rogers |
| 18092 | Ryan | Gold |
| 18038 | Lola | Reed |
| 48569 | Robert | Goshe |
CsOrders returns order data. This means you need information from all records from the NetContactData table (left table), even if there are no matches in CsOrders (right table). The records in the NetContactData table that do have matches in CsOrders will display with the matching data, but those without matches will also display.
| Business Object 2: CsOrders | |||
|---|---|---|---|
| Order Number | Bill to ID | Invoice Date | Total Charges |
| 10546 | 18220 | 6/22/2021 | 300.00 |
| 10547 | 18092 | 6/22/2021 | 350.00 |
| 10553 | 18038 | 6/22/2021 | 36.99 |
When you need to see data for everyone in the database, but you know not everyone is going to have data in one of your sources, you would use a Left Join.
In the above example, the data returned would look like the following:
| NetContactData.ID LEFT JOIN CsOrders.ID | |||||
|---|---|---|---|---|---|
| ID | First Name | Last Name | Order Number | Invoice Date | Total Charges |
| 18220 | Aaron | Rogers | 10546 | 6/22/2021 | 300.00 |
| 18092 | Ryan | Gold | 10547 | 6/22/2021 | 350.00 |
| 18038 | Lola | Reed | 10553 | 6/22/2021 | 36.99 |
| 48569 | Robert | Goshe | |||
Exists
When source A is joined to source B using Exists, you choose a property from A to match to a property in B. The query will return all rows from source A that have at least one match. Only properties from source A area available to use as Display columns in the query.
Example: You want a list of all contacts who have one or more orders billed to them this year. You need their ID, name, and email. To create this query, use NetContactData and CsOrders.
The query for the example above would use the following relationship: NetContactData.ID EXISTS CsOrders.Bill To ID
Add a filter for Order Date Within Years = 0.
The query will display columns for ID, Full Name, and Email from NetContactData. Properties from CsOrders are not available as display columns.
The query will return one row for each contact who has at least one order billed to them this year. If a contact has more than one order, they will only have one row in the query.
Not Exist
When source A is joined to source B using Not Exists, you choose a property from A to match to a property in B. The query will return all rows from source A that DO NOT have any matches. Only properties from source A area available to use as Display columns in the query.
Example: You want a list of all contacts who have NO orders billed to them this year. You need their ID, name, and email. To create this query, use NetContactData and CsOrders.
The query for the example above would use the following relationship: NetContactData.ID NOT EXISTS CsOrders.Bill To ID
Add a filter for Order Date Within Years = 0.
The query will display columns for ID, Full Name, and Email from NetContactData. Properties from CsOrders are not available as display columns. The query will return one row for each contact who has NO orders billed to them this year.
Or Exists
Similar to Exists but can be used when there is already an Exists or Not Exists join used in the query to add an additional condition.
Example: You need to return all contacts who have an order billed to them this year OR who have given a donation this year. Join NetContactData.Id to CsOrders.Bill To Id with EXISTS, and join NetContactData.Id to GiftsReceived.ID with OR EXISTS.
Or Exists and Or Not Exist must use a different combination of sources than the other joins in the query. If any of the Exist relations have already joined A and B, you cannot join A and B on a different property using Or Exists. Instead, add the secondary source (B) twice and use one instance of the source per join.
Example: You need to list all contacts who have had an order billed OR shipped to them this year. To create this query, add NetContactData as a source, then add CsOrders twice. The second addition of CsOrders is named CsOrders1.
The query for the example above would use the following relationships:
- NetContactData.Id Exists CsOrders.Bill To Id
- NetContactData.Id Or Exists CsOrders1.Ship To Id
Or Not Exist
Similar to Not Exists but can be used when there is already an Exists or Not Exists join used in the query to add an additional condition.
You need to return all contacts who have an order billed to them this year OR who have NOT given a donation this year. Join NetContactData.Id to CsOrders.Bill To Id with EXISTS, and join NetContactData.Id to GiftsReceived.ID with OR NOT EXISTS.
Or Exists and Or Not Existsmust use a different combination of sources than the other joins in the query. If any of the Exist relations have already joined A and B, you cannot join A and B on a different property using Or Not Exist. Instead, add the secondary source (B) twice and use a different instance of the secondary source for each join.
Example: You need to return all contacts who have been billed OR who have not been shipped orders this year. To create this query, add NetContactData as a source, then add CsOrders twice. The second addition of CsOrders is named CsOrders1.
The query for the example above would use the following relationships:
- NetContactData.Id Exists CsOrders.Bill To Id
- NetContactData.Id Or Not Exist CsOrders1.Ship To Id
Cross Join
There is no attempt to match a property from source A to a property in source B. Instead, all combinations of rows from each source are returned.
Example: There is a join for NetContactData.Id to CsOrders.Bill To Id using Cross Join. For each contact, the query returns one row for each order. The number of rows returned are the number of contacts multiplied by the number of orders.
This can result in an exponential number of rows and can adversely affect performance, so it is recommend to not use this join unless absolutely necessary.