Open topic with navigation

More information

Query

Query Central

Sub-Query

The Sub-Query Builder allows a user to search for records that are either in or not in a subset of records generated with an SQL statement.

Sample Advanced Query with Sub-Query

Here is an advanced query that uses a Sub-Query. Although this query is for a very specific scenario, you could apply this logic to any other area in netFORUM We want to run a query that will return a list of all individuals and all their active relationships, as long as that individual has at least one active relationship with the relationship code of Project Manager. So if Jane Doe has at least one Project Manager role (and it's active), then we want to return ALL her relationships, even those that are NOT of the code Project Manager.

Let's take a look at this query one step at a time.

Step 1: Define the Sub-Query.

You cannot simply do this:

If you add this filter, it will return ONLY relationships that have the Project Manager code and you will leave out any of the individual's other relationships.

Instead, you want to run a Sub-Query that asks "return any individuals who have any relationships that have the Project Manager role." You need a Sub-Query for this.

In this Sub-Query, we want to define a set of individual relationships, and we want to choose only those individuals who exist in that set. Therefore our Column to Query must be the Individual -> Primary Key, and we need to say that it is in the set of relationships defined by the Individual Key of those relationships.

First, select the Individual -> Primary Key as the Column to Query and for the Operator choose Is In.

Next, click the Sub-Query button to open a Sub-Query.

In your Sub-Query, you want to define the set of Project Manager relationships, and you want to define them by the Individual Key so that you can match the Individual -> Primary Key chosen in your main query with the Affiliation -> Individual Key column in your Sub-Query subselect. [Advanced Option: if you want to choose different relationship type each time you run this query, then you might want to check the Ask At Run-Time checkbox to give this query more flexibility.]

Therefore, for the Pick Table for Sub-Query, you choose Affiliation. For the Pick Column to Select for Sub-Query, you choose the ixo_ind_cst_key (Individual Key) as this is the column that corresponds to the Individual -> Primary Key.

After you have chosen your Column to Select for Sub-Query, next you must define the filter conditions in your Sub-Query. You will choose filtering conditions for the Relationship Type, and choose End Date is NULL or End Date is greater than the Current System Date, which you enter as {CurrentDate}.

Once you have entered these conditions, press Return to lock in your Sub-Query and return to the main query. Then press the OK button to add this condition to your query.

Step 2: Add Additional Filters

You will add a few more filter columns to select to output only active relationships, just as you did for the Sub-Query.

Step 3: Add Sort Order

In this query, we sort on Customer --> Sort Name and then Organization --> Organization Name.

Step 4: Add Columns to Output

Finally, in the Output area, select the Affiliation --> columns to pull in all the affiliations for the Individual. You will also want to select the Organization --> Organization Name to pull in the name of the Organization.

Step 5: Save and Run Query

Save the query and run it. Your output should appear.

Note that the Title and Organization Name in the list results are not the individual's primary title and organization, but the title and organization for each relationship.

Advanced Query with Sub-Query on Mailing List

Here is a query with a variation on the Sub-Query in the previous scenario. In this scenario, we want to output a list of individuals, as long as the individual does not belong to a certain mailing list.

You cannot simply choose this condition:


If you choose this, then you will exclude any individuals who have no mailing lists at all, and you will include individuals who have any mailing list that is NOT the Training Webinar Invite List.

Instead you need a Sub-Query that will exclude any individuals who belong in the set of mailing list recipients of a specific mailing list:

Sub-Query Builder

Defined Query

[Mailing List Detail->Mailing List Key] Is Equal To [Unsubscribe]


You can only do this with a Sub-Query. Here are the steps to add this Sub-Query:

  1. In Column to Query, choose Individual --> Primary Key. This is the "customer key" column.
  2. In Operator choose Is Not In.
  3. For Value, click the Sub-Query Builder button to build a sub-query.
  4. Now you are on the Sub-Query popup page. In the Pick Table for Sub-Query drop-down list, choose Mailing List Detail, which is the table that contains the mailing list recipients.
  5. For Pick Column to Select, choose mld_cst_key (Customer Key). This is the column that corresponds to the Individual --> Primary Key in step 1. It relates to the "customer key" of the mailing list detail.
  6. In Column to Query, choose Mailing List Detail --> Mailing List Key. For Operator, choose Equal To, and for Value choose the Unsubscribe mailing list (or whichever mailing list you want to filter on) from the available values. If you want to run this query for different mailing lists at different times, then you might want to check the Ask At Run-Time checkbox so that you will be prompted each time you run the query.
  7. Next, click the Add to Query button to add this filter to your defined query.
  8. Then click the Return button to return back to the main query page. You will now see the raw SQL statement in the value text area that is generated by the Sub-Query Builder.
  9. Next you may add any additional filters, etc., to your query.

In effect, what this query condition is doing is this: It is saying include only Individuals in which the Individual DOES NOT belong to the "unsubscribe" mailing list. If you belong to NO mailing lists, you will get the person in the query. If they belong to one or more mailing lists, none of which are NOT the "unsubscribe" one, then they will appear in the query.

Sample Sub-Query Using Is In

Suppose you're writing a query and you'd like to pull in any individuals who are members of certain committees.

If you're running this from the Individual group item, then follow the steps below to create a query with a Sub-Query to do this.


  1. First, add a new query and add a query condition and select the column Individual::Primary Key and the Is In operator as shown above.
  2. Next, click the Sub-Query Builder button to create a sub-query that will select all the committee participants in the committees you want to choose.
  3. In the Sub-Query Builder page, enter the following:
  4. Choose Committee Participant (mb_committee_x_customer) for Pick Table for Sub-Query
  5. Choose cmc_cst_key (Member Key) for Pick Column to Select for Sub-Query. Note that this column aligns with the Individual Primary Key column that you selected earlier.
  6. Select the following fields for Column to Query. The example in the screen shot below will bring in any participants who are on any of the 3 committees (JLP Committee, 2010 Committee and Charity Committee) included in the first set of parentheses who have a Start Date before or equal to the {CurrentDate} and who have an End Date that is EITHER null OR in the future (greater than {CurrentDate}). If you tend not to enter Start Dates for your committee assignments, then be sure to treat the Start Date the same way as you treat the End Date by permitting either null values or values that occur before the current date (to handle both records with no start date or with a start date in the past). Although this query does not consider committee position, you could also include committee position codes here as well although this could get complicated because each committee could have its own set of committee position codes.
  7. When you are finished with the Query Conditions, click the Return button to save this Sub-Query and go back to the main query.
  8. From the Main Query window, press the OK button to save the Query Condition.
  9. Add any additional query conditions, query columns, query sort order, etc.

An alternate way to write this query is to type a list of committee codes, enclosed with single quotes and separated by commas, in one condition, rather than adding each committee code as its own condition and adding additional "OR". This way is quicker but is more prone to error in case you mistype a code. In the example below, instead of one line for each committee, we list all the committees ('A','B','C','D','E') in one line. Either way you write the query, the results will be the same.


Be sure to enclose each code in single quotes, and separate with commas. Do not enclose this list with parentheses or brackets; the Sub-Query builder will take care of that for you.


Sub-Query Troubleshooting

If you are trying to find the table in the Pick Table for Sub-Query drop-down list, but you are not finding the table, it could be because of an underlying setup issue. Please report this issue to your Abila representative. From a technical perspective, there needs to be a List Table and From Table configured for the table that you are not seeing; your Abila representative can do this for you.

Case Studies